Table Consolidation

[sarajun_88]

I need help on developing a macro to consolidate data of table in a sheet to another.
I have got a sheet which has got a table of data that is dynamic and i want it to be consolidated into a table in a new sheet.

I am able to do it using simple sum consolidate method manually but the same isn't working via vba.

Table will be like,

saf fdg ghj hjk
A 1 2 3 7
B 65 54 54 21
A 21 584 54 523
D 12 42 58 21

Output should be like,

saf fdg ghj hjk
A 22 586 57 530
B 65 54 54 21
D 12 42 58 21

[MickG]

Try this , Results sheet2.

Please Login or Register  to view this content.

Regards Mick

[sarajun_88]

Try this , Results sheet2.

Please Login or Register  to view this content.

Regards Mick

Thanks a lot Mick!

Its working fine for the data that i shared above. But my bad that i missed to include one more thing that is it has to consolidate the same column header data too.

saf fdg saf hjk
A 1 2 3 7
B 65 54 54 21
A 21 584 54 523
D 12 42 58 21


Output:
saf fdg hjk
A 79 586 530
B 119 54 21
D 70 42 21


Really sorry for the inconvenience!

[MarvinP]

Hi Sarajun,

Your problem was why they created Power Query. If you had 2016 Excel, you could do this problem without needing a formula or VBA. Here are the steps.

Using 2016 Excel
You would click anywhere on your table of data and click on Data -> From Table and then OK in the range dialog.
Then right click on "Column1" and "UnPivot Other Columns"
Then Close and Load to a new sheet.
Right click anywhere in this new table and Insert -> Pivot Table
Drag the Column1 to Rows and Attribute to Colums and Value to Values boxes in Pivot List dialog.

Problem Done!!! No formulas, No VBA. Simply using tools built into Excel (in the newer versions)!!
See:
PQ Example for Sarajun.xlsx

[sarajun_88]

Hi Sarajun,

Your problem was why they created Power Query. If you had 2016 Excel, you could do this problem without needing a formula or VBA. Here are the steps.

Using 2016 Excel
You would click anywhere on your table of data and click on Data -> From Table and then OK in the range dialog.
Then right click on "Column1" and "UnPivot Other Columns"
Then Close and Load to a new sheet.
Right click anywhere in this new table and Insert -> Pivot Table
Drag the Column1 to Rows and Attribute to Colums and Value to Values boxes in Pivot List dialog.

Problem Done!!! No formulas, No VBA. Simply using tools built into Excel (in the newer versions)!!
See:
Attachment 514861

Hello Marvin,

Thank you for the suggestion! I knew about this manual method. But i need a vba solution so that people who don't know much about excel could also use it.

[MickG]

Try this:-

Please Login or Register  to view this content.

Regards Mick

[sarajun_88]

Try this:-

Please Login or Register  to view this content.

Regards Mick

It works perfectly! Thanks a lot!!

If possible can you please explain the code.

[sarajun_88]

Mick,

This code works fine for 5 column data but if there are more number of data columns then it doesn't work. I tried to modify the range count but it throws error. Can you please help me on this..

[sarajun_88]

Mick,

This code works fine for 5 column data but if there are more number of data columns then it doesn't work. I tried to modify the range count but it throws error. Can you please help me on this..

Finally!! got it working.. Thanks a lot!

[MickG]

You're welcome