Count number of unique values
Count number of unique values
(OP)
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?





RE: Count number of unique values
RE: Count number of unique values
RE: Count number of unique values
RE: Count number of unique values
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
RE: Count number of unique values
RE: Count number of unique values
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.
RE: Count number of unique values
Unfortunately, it still does not recalculate it the source data is changed. Oh well.
RE: Count number of unique values
TTFN
RE: Count number of unique values
1) Choose a free column where to write new data, say it is column F
2) In F3 type the following formula:
=IF(MATCH(D3;$D$3:$D$25)<ROW(D3)-ROW($D$3)+1;0;1)
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 F26) a =SUM(F3:F25) and there you get what you were looking for.
You can also use a name for $D$3:$D$25 and use the same name in ROW($D$3).
If you have blank cells in your column of part numbers, you'll need to insert an ISERROR check enclosing the MATCH function.
prex
http://www.xcalcs.com
Online tools for structural design
RE: Count number of unique values
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,"")
E5 formula : =IF(ISERROR(MATCH($D5,$D$3:$D4,0)),$D5,"")
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.
RE: Count number of unique values
=IF(MATCH(D3,$D$3:$D$25,0)<(ROW(D3)-ROW($D$3)+1),0,1)
Good thread!
RE: Count number of unique values