×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Count number of unique values
2

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

Can you use a pivot table?

RE: Count number of unique values

(OP)
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?

RE: Count number of unique values

(OP)
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?

RE: Count number of unique values

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

RE: Count number of unique values

(OP)
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.

RE: Count number of unique values

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.

RE: Count number of unique values

(OP)
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.

RE: Count number of unique values

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

TTFN

RE: Count number of unique values

Assume your column with part numbers is in D3:D25
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

Assuming your part list is D3:D10:
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

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!

RE: Count number of unique values

(OP)
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.  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources