Tech Support Guy banner
Status
Not open for further replies.
1 - 6 of 6 Posts

· Registered
Joined
·
33 Posts
Discussion Starter · #1 ·
I have a column in a spread sheet with approximately 200 rows. I need to know how many of the items are unique. Short of sorting the column and then manually counting, is there any function or trick I can use to get the number of unique items in those 200 rows. For example, if I have 201, 202, 203, 203, 204, 204, 205, 206. I would want the result count to be 6 not 8. And no I can not delete the duplicate items as there is information in other columns that is different. Any ideas/solutions?
 

· Registered
Joined
·
2,708 Posts
Try Advanced Filter -- Unique records only.

Or this array* formula from Chip Pearson**:

{=SUM(IF(FREQUENCY(IF(LEN(A1:A5)>0,MATCH(A1:A5,A1:A5,0),""),
IF(LEN(A1:A5)>0,MATCH(A1:A5,A1:A5,0),""))>0,1))}

* leave out the {}s ; entering using CTRL+Shift+Enter will create them.

** http://www.cpearson.com/excel/duplicat.htm

Rgds,
Andy
 

· Registered
Joined
·
106 Posts
This is a bit messy, but it works...

You need a non-numeric cell above your data, using your posted data, put it in calls A2 to A9

In col B2 put the formula

=MATCH(A2,A$1:A1)+MATCH(A2,A3:A$10)

copy it down to B3:B9

Hide col B

To get the total unduplicated records, use

=COUNT(A2:A9)-COUNT(B2:B9)

Ian
 
1 - 6 of 6 Posts
Status
Not open for further replies.
Top