counting the number of unique text strings in a column

nutsorters · Oct 3, 2007

I wonder if somebody could help me please.

I have a list of around 50000 names. I need to count how many are unique.

Is there an quick and easy way to do this?

Many thanks

Lewiy · Oct 3, 2007

Here is one method, the formula in B1 is copied down the column:

Excel Workbook

A

B

C

D

E

1

Person1

1

Unique records:

8

2

Person2

1

3

Person3

4

Person4

3

5

Person8

2

6

Person5

1

7

Person3

4

8

Person9

2

9

Person8

2

10

Person4

3

11

Person9

2

12

Person7

1

13

Person10

1

14

Person3

4

15

Person11

1

16

Person12

1

17

Person4

3

18

Person6

1

19

Person3

4

Sheet2

You could also use a pivot table for this.

Hope that helps!

Andrew Poulsom · Oct 3, 2007

Look here:

http://www.j-walk.com/ss/excel/usertips/tip061.htm

Peter_SSs · Oct 3, 2007

Look here:

http://www.j-walk.com/ss/excel/usertips/tip061.htm

Andrew, have you used those formulas much? The reason I am asking is ...

I just did some testing on a single column of data with about 8000 rows, including blanks.

This formula
=SUMPRODUCT((A1:A8000<>"")/COUNTIF(A1:A8000,A1:A8000&""))
took about 30 seconds to come up with a result of 960 which I believe to be correct.

The second formula in the link you provided
=SUM(IF(COUNTIF(A1:A8000,A1:A8000)=0, "", 1/COUNTIF(A1:A8000,A1:A8000))) entered with CSE
took about 1.25 minutes and came up with a result of 1589.5
Apart from taking much longer and being incorrect, its hard to imagine any count of unique entries not being an integer!!

Do you have any comments on this?

nutsorters · Oct 3, 2007

Thanks for your input.

Just to make this a bit more complicated - ideally I would like a list to be produced of the unique entries.

Any ideas?!

jindon · Oct 3, 2007

Use AdvancedFilter with unique record.

Get Help for details.

Lewiy · Oct 3, 2007

Or again, a pivot table will do this in a flash

Andrew Poulsom · Oct 3, 2007

Andrew, have you used those formulas much? The reason I am asking is ...

I just did some testing on a single column of data with about 8000 rows, including blanks.

This formula
=SUMPRODUCT((A1:A8000<>"")/COUNTIF(A1:A8000,A1:A8000&""))
took about 30 seconds to come up with a result of 960 which I believe to be correct.

The second formula in the link you provided
=SUM(IF(COUNTIF(A1:A8000,A1:A8000)=0, "", 1/COUNTIF(A1:A8000,A1:A8000))) entered with CSE
took about 1.25 minutes and came up with a result of 1589.5
Apart from taking much longer and being incorrect, its hard to imagine any count of unique entries not being an integer!!

Do you have any comments on this?

Nobody said calculation will be quick on 8000 rows.

In my test the array formula took 29 seconds and SUMPRODUCT 16 seconds. Both returned the correct answer of 26.

Peter_SSs · Oct 3, 2007

Nobody said calculation will be quick on 8000 rows.

Agreed, but the OP has 50,000 names so I noted it as speed may be an issue.

However, it was more the fact that the array formula did not produce the correct answer that alarmed me. Perhaps I will see if I can contact John W directly with the question and a sample file.

Thanks for your response

Legacy 14611 · Oct 3, 2007

This code may do what you want without too long to wait.
(a) It takes the data as being all in colA, but this is easily modified as needed.
(b) In ColC are the entries appearing, and in ColD is the count of times each appears.
(c) If you are only interested in the unique entries (i.e. those appearing once only in the list) this is likewise easily done.

Code:

Sub uniques()
Dim n As Long, i As Long, a, x
n = [a65536].End(xlUp).Row
a = Range("A1:A" & n)
With CreateObject("Scripting.Dictionary")
For i = 1 To n
    If Not .exists(a(i, 1)) Then
        .Add a(i, 1), 1
    Else: .Item(a(i, 1)) = .Item(a(i, 1)) + 1
    End If
Next i
x = Application.Transpose(Array(.keys, .items))
[c1].Resize(.Count, 2) = x
End With
End Sub

counting the number of unique text strings in a column

nutsorters

New Member

Excel Facts

Lewiy

Well-known Member

Andrew Poulsom

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

nutsorters

New Member

jindon

MrExcel MVP

Lewiy

Well-known Member

Andrew Poulsom

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Legacy 14611

Guest

Similar threads

Forum statistics

Share this page

counting the number of unique text strings in a column

New Member

Excel Facts

Well-known Member

MrExcel MVP

MrExcel MVP, Moderator

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP, Moderator

Legacy 14611

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock