Combine muliple rows into one based on criteria

Hello,

I am trying to cycle though 0-200 rows of data each with a product key and demand data. I need an output of one row for each key with a total of the demand data.

Example:

A spreadsheet like this one:

product demand 1 demand 2 ...
-----------------------------------------------
1616 50 50
1616 50 50
1617 25 25
1616 100 100
1617 50 50

total 275 275


become one like this one:

product demand 1 demand 2 ...
-----------------------------------------------
1616 200 200
1617 75 75

total 275 275



the use of a VBA macro is preferred because I already have one doing other things to get the data, so I can just incorporate it into the current one.

I have a decent grasp of VBA and good knowledge of excel.

Any ideas on how to go about this? THANKS!

Whilst you might prefer a macro, I think the easiest option is to use a pivot table. Follow the Wizard, put Product in the row field and Sum of demand1 & Sum of demand 2 in the data field.

I hope this can help you!

Sum automatically specific posts in a list

Perhaps this UDF would help. It looks down the compareRange and if the entry in that column matches the compareValue, adds the sum of all entries on that row of the SumRange to the result and then looks at the next row.
For your example, if your product is in Column A and the demands are in B & C, =SumRangeIf(A:A,1616, B:C) and =SumRowsIf(A:A, 1617, B:C) would be the syntax.

Please Login or Register  to view this content.

Even if you don't call this as a spreadsheet function, it might be helpful writting the macro you want. About that macro, on which sheet is the Product and demand data you want to process (are there only two demand columns or is the column count variable) and where do you want the results?

Assuming data are in Sheet1, outputting in Sheet2
try

Please Login or Register  to view this content.