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?

Sep 22, 2022 in Others 780 views

1 answer to this question.

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.

answered Sep 23, 2022 by
• 63,720 points

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Excel: Group rows and add minimum and maximum from two different columns within the group

You can accomplish your goal with Power ...READ MORE

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

Prevent a user from adding or removing row(s) of a Range, but allow him to do so on another Range on the same sheet

The only thing I can offer is ...READ MORE

how do i change string to a list?

suppose you have a string with a ...READ MORE

how can i randomly select items from a list?

You can also use the random library's ...READ MORE

+1 vote

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

how do i use the enumerate function inside a list?

Enumerate() method adds a counter to an ...READ MORE