Removing Duplicates but recording their values

[c.goan]

I work with Bills of Materials for large assemblies and something our purchasing department would like is for all duplicate parts top be grouped together and quantified in one cell.

For example I have an Assembly consisting of 10 Subassemblies

8 of those Subassemblies us Part A in different quiantities

Using conditional formatting I can identify duplicates and Highlight them.

What I'd really like is to pull through a new list of parts in a new sheet with no duplicates but keeping a sum of the quantities in the new list.

Any comments or suggestions are welcome.


Chris

[simeon.hruby]

Chris,

I see you are using Office 2010. In Office 2007 underneath the Data Tab there is a "Remove Duplicates" button. It should also be on 2010.

If you copy the original data to another sheet and higlight the column you wish to remove the duplicates from it will find all of the unique valuse and
put them into a nice list.

Now if you want to sum the quantities from the original data in the new sheet you should use the folowing formula.

Lets say your database has your Part Name in Column A and the Qantity in Column B on Sheet 1 and in Column A on Sheet 2 you have your unique list. Now lets put the results next to the unique values in your list on Sheet 2 in cell B1 by using the following formula.

Please Login or Register  to view this content.

Now you can use more than 1000 rows but I just used that as an example.
Hope this solves your problem!


Simeon

[c.goan]

Thank you Simeon

I've set up the formula like you've said however if is giving me back numbers I don't understand, is the quntity I now see the sum of the B Columb or the number of Duplicates?

I'm going to do some tests and try to figure out how I can use this fully.

Do the Zero's denote that the part has not been duplica

Chris

[simeon.hruby]

Chris,

The number you should see is going to be the sum of the quantity in column B. If you need to see the number of duplicates you would need to use the COUNTIF function.

You should only see zero's if there are no numbers to add toghether. I have attached this example to better demonstrate.
You should be able to view the formulas in Sheet 2 in Column B.

Let me know if you need anything else!


Simeon

[c.goan]

Thank you excelent help