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)
ouestqueAsked:
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.

DonkeyOteCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
patrickabCommented:
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
        coll.Add celle, celle
    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

Open in new window

ouestque-04.xls
0
DonkeyOteCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

patrickabCommented:
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
        coll.Add celle, celle
    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
        coll.Add celle, celle
    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

Open in new window

ouestque-05.xls
0
patrickabCommented:
>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
DonkeyOteCommented:
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
DonkeyOteCommented:
...and by "alter" I meant "alert" ... if only I could type !
0
patrickabCommented:
DonkeyOte,

Thanks for your response.

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
DonkeyOteCommented:
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
patrickabCommented:
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
        coll.Add celle, celle
    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
        coll.Add celle, celle
    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

Open in new window

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.