Unique values & qty of each repeated value

**digdug** · 11-01-2007, 10:00 AM

I am trying to extract all unique values as well as the quantity of repeated values from a list of non-consecutive cells. Basically, I need to create a parts list based on part numbers which are created through a series of selections. The data is then concatenated into a single cell, and then all unique items are listed and quantified. I am able to generate the list, but can't figure out how to get a list of the unique items and quantities extracted into a separate list without the blank cells in between.

ATTACHED IS THE SCREENSHOT.
Thanks

**Ron Coderre** · 11-01-2007, 10:09 AM

It seems like a Pivot Table could do what you're asking for...

It would automatically:
List each unique part number
and the quantity.

If that is something you're interested in:

Assuming ParNums are in Col_A, with A1: PartNum

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW: Drag the PartNum field here
COLUMN: (Leave this area blank)
DATA: Drag the PartNum field here....again
If it doesn't list as Count of PartNum...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table…Click [Finish].

That will list each PartNum and the associated count.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?

**digdug** · 11-01-2007, 11:25 AM

Thanks Ron.

Your advice worked better than I even expected.

I've never used Pivot tables before...don't ask me why...but now they will now be a great assett in my arsenal of analysis tools.

The more I learn about excel the more I realize I need to keep learning.

Also, thanks for such a quick response!

Blessings! D

Unique values & qty of each repeated value

LinkBack

Thread Tools

Rate This Thread

Display