Combining data in duplicate rows, Excel 2007

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.

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.

hi you can apply a pivot table to solve your problem as well
u just need to put name at row side and column at field area

This just saved me a monster amount of time... Thanks...

i want to find rows which have common col1 & col2

please help

a b c d e f g h i
30-04-13 ABAN SELL NSE 35 295.54 10343.9 -35 -10343.9
02-05-13 ABAN BUY NSE 1 325.1 325.1 1 325.1
02-05-13 ABAN SELL NSE 1 329.9 329.9 -1 -329.9
21-05-13 ABAN BUY NSE 26 324.98 8449.48 26 8449.48
23-04-2013 ADANIENT BUY NSE 20 226.26 4525.2 20 4525.2
23-04-2013 ADANIENT SELL NSE 20 226.13 4522.6 -20 -4522.6
29-04-13 AMBUJACEM BUY NSE 5 184.53 922.65 5 922.65
30-04-13 AMBUJACEM BUY NSE 10 185.33 1853.3 10 1853.3
03-05-13 AMBUJACEM BUY NSE 5 190.8 954 5 954
06-05-13 AMBUJACEM BUY NSE 5 186.73 933.65 5 933.65
03-06-13 AMBUJACEM BUY NSE 5 181.71 908.55 5 908.55
24-05-13 BANKINDIA BUY NSE 20 302.89 6057.8 20 6057.8
29-05-13 BANKINDIA BUY NSE 5 295.16 1475.8 5 1475.8
26-04-13 BHARTIARTL SELL NSE 20 317.17 6343.4 -20 -6343.4
02-05-13 BHARTIARTL BUY NSE 7 314.68 2202.76 7 2202.76
02-05-13 BHARTIARTL SELL NSE 7 321.62 2251.34 -7 -2251.34
07-05-13 BHARTIARTL SELL NSE 7 329.51 2306.57 -7 -2306.57
21-05-13 BHARTIARTL BUY NSE 10 305.8 3058 10 3058
23-05-13 BHARTIARTL BUY NSE 5 302.19 1510.95 5 1510.95
30-05-13 BHARTIARTL BUY NSE 10 310.72 3107.2 10 3107.2
31-05-13 BHARTIARTL BUY NSE 5 306.71 1533.55 5 1533.55
06-06-13 BHARTIARTL BUY NSE 5 294.08 1470.4 5 1470.4
06-06-13 BHARTIARTL SELL NSE 5 295.26 1476.3 -5 -1476.3
10-06-13 BHARTIARTL BUY NSE 5 283.08 1415.4 5 1415.4

Originally Posted by holin_168

I have written a solution of combining data in duplicate rows, excel 2007 over here please check it if it can help you or not?

I clicked on your solution, but the Firewall at work blocks you. I can understand why. Can you explain what you did? Was it a macro? I have this exact same problem and can identify the duplicates, but I need the data in the subsequent columns combined. I can't delete the duplicate line without doing that.

harikmaheshwari, dbakker, try our add-in - Combine Rows Wizard.

It does exactly what you need – on the first step you select the columns to check for duplicates, and on the next step you specify in which columns to combine the data and whether to merge all records or unique entries only.

This is a shareware tool and I believe the first 15 days of free usage are more than enough to cope with a particular task