fastest way to consolidate large data

Hi Guys,

I have a large data (more than 50k rows) in a spreadsheet and i want to consolidate the information's.

here is the sample source data.

Source Data
code amt1 amt2 amt3
123456 $16.00 $0.00 $0.00
789011 $0.00 $18.00 $0.00
123456 $12.00 $5.00 $0.00
123456 $0.00 $0.00 $7.00
111111 $11.00 $1.00 $3.00
789011 $22.00 $0.00 $0.00

and the output should be.

Output
code amt1 amt2 amt3
123456 $28.00 $5.00 $7.00
789011 $22.00 $18.00 $0.00
111111 $11.00 $1.00 $3.00

currently i'm using the consolidate function of excel inside my vba (which is working fine) but i took forever before it finished, i'm just wondering if there is an alternative way which is more faster.

Thanks a lot.
jdlc

Assuming header in row1 and the data starts from A2

Please Login or Register  to view this content.

I would use a pivot table for this. I'm not sure how to do pivot tables in pre-2007 though

Originally Posted by jindon

Assuming header in row1 and the data starts from A2

Please Login or Register  to view this content.

many thanks jindon, i appreciate your quick response, but i tried it with 50 thousands records and the result is much slower than the consolidate function of excel. anyways many thanks again if ever you have any solution please let me know.

Originally Posted by Speshul

I would use a pivot table for this. I'm not sure how to do pivot tables in pre-2007 though

hi speshul, thanks buddy, that would be nice, but the output will be use for the mail merge and analysys.

Excel Built-In function is the fastest I think.

Any difference in time from the code I posted?

Please Login or Register  to view this content.

OOps

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Originally Posted by jindon

OOps

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

genious! thanks boss!