asked on

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)

ASKER CERTIFIED SOLUTION

DonkeyOte

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

patrickab

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

DonkeyOte

Patrick, the same VBA thread can be located here: https://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.

patrickab

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

patrickab

>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

DonkeyOte

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.

DonkeyOte

...and by "alter" I meant "alert" ... if only I could type !

patrickab

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

DonkeyOte

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

SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial