# Excel: Count uniques in column

I have an excel file with first name in column A and last name in column B.

i.e. If I have the following:
A              B
Tom          Petty
Tom          Petty
Tom          Cat
Tom          Cat
Sam          Silo
Bob           Better
Better        Bob

Question1> What formula would I use, if I were going to find number of distinct names between column A and B? (i.e. The formula will say: 5 (Because there are 5 distinct names between column A and column B.)

Question2> What formula would I use if I were going to find number of distinct entries in column A only? (i.e. The formula will say: 4 (Because there are 4 distinct names in column A only)
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Question 2 is simpler - eg:

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

Question 1 is harder (if we ignore possibility of concatenating in one column)

=SUMPRODUCT(--(A1:A7&B1:B7<>""),--(MATCH(A1:A7&" "&B1:B7,A1:A7&" "&B1:B7,0)=ROW(A1:A7)))
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
ouestque,

The code below is in the attached file. Press the button to obtain the results.

Patrick
``````Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long

With Sheets("Sheet1")
Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "B").End(xlUp))

For Each celle In rng
On Error Resume Next
Next celle

Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp)).ClearContents
For i = 1 To coll.Count
.Cells(1, "D") = coll.Count
.Cells(i + 1, "D") = coll(i)
Next i

End With

End Sub
``````
ouestque-04.xls
0
Commented:
Patrick, the same VBA thread can be located here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q__26023983.html

I believe by virtue of "which formula" I think we can deduce that this is not VBA related.
0
Commented:
ouestque,

The code below is in the attached file. The first results are for column A and the second are for columns A & B. Press the button to obtain the results.

Patrick
``````Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long

With Sheets("Sheet1")
Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "B").End(xlUp))

For Each celle In rng
On Error Resume Next
Next celle

Range(.Cells(1, "E"), .Cells(.Rows.Count, "E").End(xlUp)).ClearContents
For i = 1 To coll.Count
.Cells(1, "E") = coll.Count
.Cells(i + 1, "E") = coll(i)
Next i

Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))

For i = 1 To coll.Count
coll.Remove 1
Next i

For Each celle In rng
On Error Resume Next
Next celle

Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp)).ClearContents
For i = 1 To coll.Count
.Cells(1, "D") = "Column A = " & coll.Count
.Cells(i + 1, "D") = coll(i)
Next i

End With

End Sub
``````
ouestque-05.xls
0
Commented:
>I believe by virtue of "which formula" I think we can deduce that this is not VBA related.

And I'm sure you noticed that the previous thread was a request for a VBA solution. It is up to the Asker to decide which solution to adopt and it is up to me as to how I wish to offer a solution. If my solution is not wanted then it will not be accepted - not much more complicated than that.

Patrick
0
Commented:
Patrick, I was not trying to be argumentative I was merely trying to alter you to the fact that your VBA would be better off in the OP's VBA question on the same subject.  Your call of course.

On an aside, for this question at least I think you missed the point in so far as Q1 relates to the unique combinations of A & B - you might want to revisit whatever VBA you opt to post to this thread on that basis.
0
Commented:
...and by "alter" I meant "alert" ... if only I could type !
0
Commented:
DonkeyOte,

I really don't understand Q1 so that's why I've gone for unique items in columns A and B. Is it the unique combinations in columns A & B? If it is then I'll add that to the code - thanks for the 'alter' or even the 'alert'!

Patrick
0
Commented:
Based on the suggested result of 5 I am presuming it to be combinations thereof - ie Tom Petty, Tom Cat, Sam Silo, Bob Better, Better Bob
0
Commented:
ouestque,

The code below is in the attached file.

Hopefully I've covered it every which way. Press the button to obtain the results.

Patrick
``````Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long

With Sheets("Sheet1")
Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))

For Each celle In rng
On Error Resume Next
Next celle

Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp)).ClearContents
For i = 1 To coll.Count
.Cells(1, "D") = "Column A = " & coll.Count
.Cells(i + 1, "D") = coll(i)
Next i

Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "B").End(xlUp))

For i = 1 To coll.Count
coll.Remove 1
Next i

For Each celle In rng
On Error Resume Next
Next celle

Range(.Cells(1, "E"), .Cells(.Rows.Count, "E").End(xlUp)).ClearContents
For i = 1 To coll.Count
.Cells(1, "E") = "Column A & B = " & coll.Count
.Cells(i + 1, "E") = coll(i)
Next i

Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))

For i = 1 To coll.Count
coll.Remove 1
Next i

For Each celle In rng
On Error Resume Next
coll.Add celle & celle.Offset(0, 1), celle & celle.Offset(0, 1)
Next celle

Range(.Cells(1, "F"), .Cells(.Rows.Count, "F").End(xlUp)).ClearContents
For i = 1 To coll.Count
.Cells(1, "F") = "Column A & B combined uniques = " & coll.Count
.Cells(i + 1, "F") = coll(i)
Next i

End With

End Sub
``````
ouestque-06.xls
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.