Count unique entries

[frankerrington]

A column has 26,000 entries. Many are repeats. I'd like to count just the unique occurrences

 

[maxflia10]

Here's a solution usng the Morefunc add-in

http://216.92.17.166/board/viewtopic.php?topic=25282&forum=2

 

[Aladin Akyurek]

On 2002-10-21 16:06, frankerrington wrote:
A column has 26,000 entries. Many are repeats. I'd like to count just the unique occurrences

In ascending order of preference...

If there are no formula generated blanks among your entries, use:

=COUNTDIFF(Range)

where Range refers to the range that houses those 26K entries.

COUNTDIFF is part of the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html

A costly alternative is the following array-formula...

=SUM(IF(LEN(Range),1/COUNTIF(Range,Range)))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

 

[Corticus]

Without the add-in,

(I was already done with my solution when the first one popped up, so here's mine too)

I set up a sheet as follows:

CountUnique.xls
	A	B	C	D
1	A	A	Unique
2	C	C	10
3	D	D
4	F	F
5	F	S
6	S	E
7	E	W
8	W	R
9	D	T
10	F	G
11	D
12	E
13	W
14	R
15	T
16	F
17	G
18	D
19	D
Sheet1

Then put the following code behind the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True

Application.EnableEvents = True
End Sub

HTH,
Corticus
This message was edited by Corticus on 2002-10-21 16:22

 

[frankerrington]

Got it! Thanks for all your help!

 

[Mark W.]

On 2002-10-21 16:06, frankerrington wrote:
A column has 26,000 entries. Many are repeats. I'd like to count just the unique occurrences

If your column (e.g., A:A) consists only of numeric values you could employ the following array formula...

{=COUNT(IF(FREQUENCY(A:A,A:A),1))}

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-10-21 16:54