Condensing multiple entries in excel into one row

Question

I have a spreadsheet in excel where in the leftmost column I have order numbers and the corresponding cell to each cell in the order number columns contain the dollar amount for that particular order. Orders from the same customer have the same order number but different dollar amounts and listed separately in the spreadsheet. Is there a way to systematically condense these orders into a single order for each customer with a corresponding dollar amount equal to the sum of all the orders?

order number    Dollar Amount           order number    dollar amount
1234                $100                     1234         $1,000 
1234                $200                     2345         $1,100 
1234                $300                
1234                $400                
2345                $500                
2345                $600

hackeryarn hackeryarn · Accepted Answer · 2014-07-07T21:58:47

There is an easy way without any functions or script to do this, but doesn't look exactly like what you want.

Please see steps below:

Sort the data in order number
Select the the columns order number and Dollar Amount
Go to Data -> Subtotal
Select the following settings:

Subtotal Settings

You should now have all the information you are asking for just not in a separate column. It should look like this.

order number    Dollar Amount
1234    $100 
1234    $200 
1234    $300 
1234    $400 
1234 Total  $1,000 
2345    $500 
2345    $600 
2345 Total  $1,100 
Grand Total $2,100

Condensing multiple entries in excel into one row

3 Answers