Big data collation

[Rajesh S]

Dear Friends,

I am working on a big data comprising 185 columns with 1.5 L rows, mainly comprising of 50000 Employee records for 3 months. (File size comes to nearly 140 MB, hence not possible to share the entire file). I am sharing the sample file for your reference.

I want to consolidate the files based on the Deputee ID (Refer column A) with the values appearing in column BJ to GB. i.e., each & every employee will have maximum of 3 records which needs to be summed up to arrive at the consolidated figures paid towards 3 months.

Is there any way to help me with a macro which is fast to carry out this exercise.

Thanks in advance for your help

Regards

S Rajesh

 

[NARAYANK991]

Hi ,

It might be better if you explore whether you can use Power Pivot to handle such volumes of data.

Narayan

 

[Hui]

Have you tried a Pivot Table ?

Is this the format you wanted?

 

[Rajesh S]

Thanks Narayan & Hui for your suggestion.

Since I work with Office 2010 Professional version, Power pivot add-in is not part of it & could not work it out.

Hi Hui,

Format by which output is required is based on the Deputee ID (Column A). Sum is required for all headers available in column BJ to GB separately under the same header. ie., For an employee what are all the sum of components paid.

Output required is:



Can you please help me out in this with a macro?

Regards

S Rajesh

 

[Hui]

What is wrong with the pivot table approach ?

Simply add all the remaining fields as Sum Values to the PT

 

[Rajesh S]

Hi Hui,

Thanks. I have already tried that but since the file size is very huge, excel does not have sufficient memory to carry it out for all fields.

Also, this will be a huge exercise considering the attempts involved in it viz., 4 files with similar data for 4 quarters.

Hence the query to find out a simple solution to the problem.

Regards,

S Rajesh

 

[Hui]

You can't do Big Data cheaply/easily

I'll think about a VBA function but recommend Power Pivot as a better way to simplify this

I'd also suggest a PC with 16GB+ of RAM
Excel 2016 64Bit which will maximize the use of the Memory

 

[Chihiro]

@Rajesh S
For Office 2010, PowerPivot is free add-in (I have one installed). Check link.
https://msdn.microsoft.com/en-us/library/gg413462(v=sql.110).aspx