Removing duplicates from Excel rows by adding values of some columns

Question

I have a 500-row Excel file with product information and their quantities in various programs. These 500 rows include identical products in various quantities. In order to construct the bottom table from the above one, I need to delete duplicates and add up amounts such that there is only one row with productID D1 rather than 5 rows with it.

 

People advised uploading the data in the database and having sum(quantity1), and sum(quantity2),... but I have 150 columns so I would not be able to build a query for that. I discovered a similar topic on StackOverflow.. (Removing duplicate rows by adding column value)

I am thinking of writing a python script but I am not sure how I can handle the duplicates. Can someone please help me with this?

Answer 1

Copy the first three columns to another sheet or location. Make use of "Remove Duplicates." Utilize a formula similar to this one to determine the total quantities:

=SUMIFS($D$2:$D$500, $A$2:$A$500, $A2, $B$2:$B$500, $B2, $C$2:$C$500, $C2)

Then drag the formula to find other quantities.