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

#### punster

· Registered
Joined
·
33 Posts
Discussion Starter · ·
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?

#### XL Guru

· 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

#### Gazornenplat

· 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

#### punster

· Registered
Joined
·
33 Posts
Discussion Starter · ·
Thanks - so far the simplest - the Advanced Filter of Unique records only works for my needs. Will save other more complex in case need for future use.

#### maxflia10

· Registered
Joined
·
331 Posts
Here's a couple other options, although I see you've settled on Advanced Filter.

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))