How do I combine duplicates in Excel?

Question

I need to make a cost/profit+loss spreadsheet. The math part I have down. But i would like to make the sheet so that if I enter a row and if in the serial number column I enter a duplicate serial number (work done twice) that it combines the two rows and just deletes the duplicate. Meaning that if serial #3565 had $100 done one day, and $225 another that it takes the two rows, combines them and shows just one row with $325 worth of work. How do I do this? thanks for the help. Even if its a bit complicated in functions I'd like to know how just cause it would make it a lot easier.

Answer 1

Excel's not capable of doing that automatically. It might be possible in Access.

Answer 2

To do exactly what you want would require a fairly complicated Visual Basic macro, and there's quite a steep learning curve for Visual Basic.

There is one other approach which you might find workable. It's Excel's subtotal capability:

First you would sort your database on the serial no. column. You also need header labels for each column. Then select the whole database, incl. the header labels, and under the Data menu, select Subtotals... Then in the resulting dialog box, you tell Excel that on each break in the Serial No. field (i.e. each time the serial no. changes) you want to subtotal other fields, like the $ value of work done.

When you click OK, Excel will create subtotals plus a levels box on the left that will allow you to collapse the database to just the subtotal level.

Using the same menu, all subtotals can be removed.

The only thing this approach will not do is to combine the rows for each serial no. into just one row.

Answer 3

MS Excel come with some great tools to do a lot of operations. You coud actually write a VBA code to do the job for you. But a simple and easy way to create a conditional formating to detect all duplicate rows and then use the SUM function to do the rest.

Click on Formt -> Conditional Formation -> Formulas and enter some thing like this =COUNTIF(A2:A, A2)>1. I am sure you will find the in one or two click.

Hope that will help!

Good Luck!

Answer 4

If you doing accounts, you wouldn't want to write something that wipe out or consolidate duplicates entries into one. Duplicates are there for a reason. Duplicates can be unfilled orders or order that are splitted up. There are hidden costs to account for.

Use pivottables. They will consolidate all the duplicates nicely for you and keep an audit trail.

Answer 5

Excel now has a "Consolidate" button under the Data tab. This just saved me a couple of hours of work by hitting one button!