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 695 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.

• 63,720 points

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...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