Combining data in duplicate rows, Excel 2007

**tsdet08** · 08-15-2008, 04:10 AM

Okay so I'm hoping someone can help me.

I have a looooong (about 180 000 rows) list of part numbers, descriptions, prices and quantities. There are many duplicate part numbers. I need to go through and find the duplicates, add all their quantities into one cell and delete the remaining duplicates. I have used conditional formatting to find the duplicates easier but cannot use the remove duplicates button as it will delete the whole row and I need the quantity information in each row.

Does anyone have any ideas on a formula or macro I can set up to search the part number column (F) for duplicates and add the figures in the quantity column (J) and THEN delete the duplicate part number rows?

Maybe I need to put the new combined information in another sheet?

Technically I am an advanced user of Excel but have been out of the game for a bit so am a bit rusty so please explain as simply as possible

Many thanks in advance guys, hopefully I can find a solution or I'm going to have many many days of manually going through the whole lot.

**Stuie** · 08-15-2008, 04:16 AM

Hi tsdet08,

I oftern find that when searching out duplicates its easiest to sort the data by that column and then run this formula down the side.

=IF(A2<>A1,0,1)

this is will then make the first instance of a new value a 0 and all duplicates will have a 1 next to them.

you can then paste specials on the column with the forumla in and then re sort the data by that column and delete all rows with a 1 in.

(thats after having sorted out the sub totals)

If im missing something let me know

EDIT: i have attached an example

**tsdet08** · 08-17-2008, 09:51 PM

Took me a while to understand it but I did in the end and it works perfectly!

Thank you sooo much for your help. You've saved me a LOT of work. I knew that there had to be an easier way to do it.

Thanks again.