Count number of unique values 2

[dgallup]

How can I find out how many different values I have in a data range? I have a spreadsheet of hundreds of bills of material. Each column has a type of component. Most on the components are used in multiple finished goods. How can I find out how many different unique part numbers I have in each column?

 

[melone]

Can you use a pivot table?

 

[dgallup]

I hate to display my ignorance but I don't know what a pivot table is or how to use one. Can you explain how that would work?

 

[dgallup]

Well, I looked up the help on pivot tables, I don't see that they do anything useful in this case. No new functions, just window dressing. Maybe I'm missing something?

 

[IRstuff]

Are your part numbers purely numbers?

If so, you can use the frequency function to generate a histogram table of any given column and then count the none-zero entries.

You would need a list containing all the possible part numbers. The Frequency function then tallies the frequency of occurrence of each part number, so you'd need an array formula for that. Also, you'd need to wrap an if statement around that to blank out counts that are 0. Then the Count function will count up the non-blank cells.

TTFN

 

[dgallup]

Part numbers have letters and dashes as well as numbers. Here is what I have found so far. I can copy each column and then sort it alphabetically. Then do a data/subtotal on the sorted column. This gives me a subtotal for each part number. Then I can count the number of subtotals to get the number of unique part numbers. Unfortunately, I have to do this individually for each column as subtotal does not work unless the data is sorted. This means that it all has to be redone each time a new finished part number is added to the data.

 

[MintJulep]

Try this:

Data|Filter|Advanced Filter

for the List range, select a column.

For the Criteria Range, select a cell into which you have typed an *

Check the Unique Records Only Box.

Select the Copy to another location option.

select a cell for the top of the filtered list to be copied to.

Click OK

Poof! A listing of the unique items in the column.

Using this command seems a bit wierd and very unintuitive to me. There must be a column header cell. It will work, but you might need to play with it for a while to figure out how to use it correclty.

 

[dgallup]

Thanks MintJulep, that works better than what I was doing. The criteria range part is really weird, I don't see why I shouldn't be able to just enter a * rather than reference a cell with a * in it.

Unfortunately, it still does not recalculate it the source data is changed. Oh well.

 

[IRstuff]

What you can do is to map part numbers to a pure number and then generate the frequency lists.

TTFN

 

[prex]

Assume your column with part numbers is in D325
1) Choose a free column where to write new data, say it is column F
2) In F3 type the following formula:
[tt]=IF(MATCH(D3;$D$3:$D$25)<ROW(D3)-ROW($D$3)+1;0;1)[/tt]
3) Copy that cell down to F25
Now in F3:F25 you should see a 1 at the first occurrence of each part number, and a 0 where a part number repeats
4) Insert somewhere (possibly in [tt]F26[/tt]) a [tt]=SUM(F3:F25)[/tt] and there you get what you were looking for.
You can also use a name for [tt]$D$3:$D$25[/tt] and use the same name in [tt]ROW($D$3)[/tt].
If you have blank cells in your column of part numbers, you'll need to insert an [tt]ISERROR[/tt] check enclosing the [tt]MATCH[/tt] function.

prex

http://www.xcalcs.com

Online tools for structural design

 

[killswitch]

Assuming your part list is D310:
The list of unique values will be in E3:E10.
E3 formula : = D3 - since the first value in column D will be used in column E.
E4 formula : =IF(ISERROR(MATCH($D4,$D$3:$D3,0)),$D4,&quot;&quot
E5 formula : =IF(ISERROR(MATCH($D5,$D$3:$D4,0)),$D5,&quot;&quot
continue thru E10.

If it doesn't find a match in the values listed in column D above the current row, it copies the column D value to E.
Else blank cell.

 

[bltseattle]

IMO, Prex's solution is elegantly simple, but to get it to work I had to add the 3rd argument to the MATCH function (if omitted the default is 1):

=IF(MATCH(D3,$D$3:$D$25,0)<(ROW(D3)-ROW($D$3)+1),0,1)



Good thread!

 

[dgallup]

Great! Btlseattle nailed it. I had a problem with Prex's solution, I think it is the semi colons where Bltseattle has commas. This updates as the data changes and I can insert a row to add new finished goods. Thanks a lot to everyone who replied.