counting the number of unique text strings in a column

N

nutsorters

New Member
Joined
Sep 28, 2007
Messages
4
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Here is one method, the formula in B1 is copied down the column:
Excel Workbook
ABCDE
1Person11Unique records:8
2Person21
3Person34
4Person43
5Person82
6Person51
7Person34
8Person92
9Person82
10Person43
11Person92
12Person71
13Person101
14Person34
15Person111
16Person121
17Person43
18Person61
19Person34
Sheet2


You could also use a pivot table for this.

Hope that helps! :)
 
Upvote 0
Look here:

http://www.j-walk.com/ss/excel/usertips/tip061.htm
Click to expand...
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?
 
Upvote 0
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?!
 
Upvote 0
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?
Click to expand...

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.
 
Upvote 0
Nobody said calculation will be quick on 8000 rows.
Click to expand...
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 :)
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
285
RobP
R
A
  • Solved
Count unique IDs with a wildcard criteria for another column
Replies
2
Views
161
ausswe
A
N
  • Solved
COUNTA Function not counting any cells
Replies
1
Views
114
nithchun
N
R
  • Solved
VBA question regarding multiple instances of strings
Replies
8
Views
328
Peter_SSs
Peter_SSs
D
  • Question Question
Unique Function
Replies
3
Views
82
Dave Patton
D

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top