Need easy solution for summing 2 columns after grouping on 16 coumns for over 200k records in EXCEl VBA

I have an excel sheet with around 18 columns. The last 2 columns being amounts. I have a situation wherein I need to repeatedly do grouping.

For eg: Consider the first 16 columns, lets say it level 16. I need to group on all these 16 columns and sum the amounts in the last 2 columns. Then comes level 15, wherein i now group on the 15 colmns and sum the last 2 columns and so on...till i reach level 1

The problem here is that i am dealing with 200k+ records. One particular approach i took was first copy the entire 200k+ records to another sheet, then based on current level(say 16) I use CONCATANATE(all 16 cols) to create a string and then use the

 =SUMIF(entire 200k concatan开发者_运维问答ate string,first cell of concatenate string,entire range of coulmn to be summed)

The use the same for the other column to be summed. Now since embedding this formula to each cell in loop will cause hell lot of time, I use range copy paste to do it. But still copy pasting the formula to 200k rows hangs it a bit.

After doing that I use the .removeduplicates method to remove off any duplicate records based on the concatenated string . The problem with this is that the SUMIF formula has dependency on those records removed by .removeduplicates method :(

For this sake, i have first embed the formulas in last 2 columns, then copy paste them in seprate columns using copypastespecial as "values" and then use the .removeduplicates method

This is causing hell load of time and resources and excel tends up hanging everytime. Am not a VBA expert so am pretty sure there might be a better logic out there :(. Am using excel 2007

Please help!!!

I would recommend using a pivot table. Start by adding all the 16 column fields to the report filter, and the 2 you want to sum to the values area.That gives you the first grouping . them move a column to the Row area for the second grouping etc etc

Or in 2007 you could try using SUMIFS rather than SUMIF, but a Pivot would be better