Adding then deleting the Duplicates

**legeorgia** · 07-23-2012, 03:39 PM

Hello,
Please help me with the following:

I have 3 columns:
The first column shows the part numbers
The second column shows the description
The third column shows the quantity

What I need help with, is how to you calculate multiple part numbers and showing it only once. For example. Below, 180541-102, shows up 5 times. I only want it to show up once and like this:

180541-102 SDB Assmbly 87 (I only care of the part number - whatever the description states is fine).

Thank you much in advance, here is a sample of my worksheet.

178921-301 INTEGRATED NOISE CANCELLING AUDO JACK 60
179029-101 AVC-D 1
180541-102 SDB ASSMBLY 15
180541-102 SEAT DATA BOX 12
180541-102 SDB ASSMBLY 58
180541-102 SEAT DATA BOX 1
180541-102 SEAT DATA BOX 1
180717-101 DSU-D3 4
180725-101 SEAT POWER BOX 15
180725-101 SEAT POWER BOX 11
180725-101 SEAT POWER BOX 52
180725-101 SEAT POWER BOX 1
180725-101 SEAT POWER BOX 2
180725-101 SEAT POWER BOX 1
180725-101 SEAT POWER BOX 1
180761-101 ADB-GII 7
180770-403 SVDU-8.9W Gen III with TS and PS Arm-Mounted 18
180771-403 SVDU-8.9W Gen III with TS and PS SB 136
180773-403 SVDU-10.6W Gen III with TS and PF Arm-Mounted with tilt sensor (Dark Grey BAC70961) 7
180776-203 SVDU 12.1, GEN III, WIDE, IN-ARM 35
180811-003 OUTLET UNIT, AC, IN-SEAT POWER SUPPLY 35
180811-003 OUTLET UNIT, AC, IN-SEAT POWER SUPPLY 25
180811-003 Outlet Unit, 110V AC UK, 60 Hz - OUTLET UNIT (UK PLUG) (Dark Gray BAC 70961) 102
180811-003 OUTLET UNIT, AC, IN-SEAT POWER SUPPLY 3
180811-003 OUTLET UNIT, AC, IN-SEAT POWER SUPPLY 6
180811-003 OUTLET UNIT, AC, IN-SEAT POWER SUPPLY 3
180874-101 KEYBOARD 1
180888-801 iCMT Gen3 12.1" without Credit Card Reader 1
180888-901 iCMT Gen3 12.1" with Credit Card Reader 1
180889-403 SVDU-10.6W Gen III with TS and PF SB (Dark Grey BAC70961) 18

**ChemistB** · 07-23-2012, 03:46 PM

Select your data.
In Excel 2007, on the Data Tab Key, "Remove Duplicates" Choose Column A> OK
Does that work for you?
Might want to copy and paste your data to a new page first as you will lose all your duplicates.

Oops, didn't see the part about consolidating quantity.
First, in Column D
In D2,
=SUMIF($A$2:$A$31,A2,$C$2:$C$31) copied down
Then copy paste special > Values from col D to Col C. Then do that first part.

**legeorgia** · 07-23-2012, 04:30 PM

Thank you so much. This helped a lot. Thank you.

---------- Post added at 01:11 PM ---------- Previous post was at 01:05 PM ----------

After trying it for the first time it worked great. However when I go to copy paste it, due to the formula, it all went to zero. Is there another way that it work without upsetting the formula?

---------- Post added at 01:14 PM ---------- Previous post was at 01:11 PM ----------

Also, If I manually delete the duplicated rows the calculation changes.

---------- Post added at 01:30 PM ---------- Previous post was at 01:14 PM ----------

Ok, I was able to get it going perfectly. I have to hide column C but it's not a big deal. Thanks again for everything.