Combining duplicate rows and adding values in Excel sheet

Question

Good day folks! I really hope you will be able to help with the following. I searched everywhere, but existing formulas designed for 2-3 columns and i have more then that. I also tried pivot tables but not sure how to do it properly. I have following columns and rows:

Brand   Style#  UPC          QOH  Price   Dim1_Name      Dim1   Description      Department
Adidas  AJ3858  889133106380  2   $85.00  Clothing Size  Small    SP LXE ZIP PANT  Bottoms
Adidas  AJ3858  889133106380  2   $85.00  Clothing Size  Small    SP LXE ZIP PANT  Bottoms
Adidas  AJ3858  889133106371  1   $85.00  Clothing Size  Medium  SP LXE ZIP PANT  Bottoms
Adidas  AJ3858  889133106371  3   $85.00  Clothing Size  Medium  SP LXE ZIP PANT  Bottoms
Adidas  AJ3858  889133106371  2   $85.00  Clothing Size  Medium  SP LXE ZIP PANT  Bottoms
Adidas  AJ3171  889133106365  2   $60.00  Clothing Size  Large   ZIP PANT         Bottoms
Adidas  AJ3171  889133106365  1   $60.00  Clothing Size  Large   ZIP PANT         Bottoms
Puma    RM-145  886148756658  1   $25.00  Clothing Size  Small   TEE              Tops
Puma    RM-145  886148756658  4   $25.00  Clothing Size  Small   TEE              Tops
Puma    RM-145  886148756658  0   $25.00  Clothing Size  Small   TEE              Tops
Puma    RM-145  886148756613  1   $30.00  Clothing Size  X-Large TEE              Tops
Puma    RM-145  886148756613  1   $30.00  Clothing Size  X-Large TEE              Tops

There are couple more columns for some items like Dim2_Name and Dim2 Value but I made it like this just to give you an idea. There was another column which contained store names, and thats where all the duplicate entries came from. I was looking for formula or way to sum QOH column which contains quantity of items for duplicate items in same color or size. Like this:

Brand   Style#  UPC          QOH  Price   Dim1_Name      Dim1   Description      Department
Adidas  AJ3858  889133106380  4   $85.00  Clothing Size  Small    SP LXE ZIP PANT  Bottoms
Adidas  AJ3858  889133106371  6   $85.00  Clothing Size  Medium  SP LXE ZIP PANT  Bottoms
Adidas  AJ3171  889133106365  3   $60.00  Clothing Size  Large   ZIP PANT         Bottoms
Puma    RM-145  886148756658  5   $25.00  Clothing Size  Small   TEE              Tops
Puma    RM-145  886148756613  2   $30.00  Clothing Size  X-Large TEE              Tops

Placement of columns can be random and data can be sorted as well. Thank you for your time and patience!

Answer 1

This can be done with a pivot table, but the QOH column will need to be at the very right of the final output table.

Create a pivot table, drag Brand, Style, UPC, Price, Dim1_Name, Dim1, Description, Department into the Rows area. Drag QOH into the Values area, where it will default to be summed.

On the Design ribbon of the Pivot Table tools, set the Report Layout to "Show in Tabular Form" and then again to "Repeat all Item Labels". Turn the Subtotals and Grand Totals off.

You can change the sort order by value of the QOH total, if required.