CODEWITHSUNDEEP
vbaexcelexcel-formulacombinations
Jhaenerys
Jhaenerys

Reputation: 13

How can I find all of the combinations appear in excel?

I have 5 coloumns, each of them contains a lot of different data. Here's an example: 

A B C D E F  
1 0 1 1 0 1  
2 1 0 0 1 2  
1 2 0 1 2 1  
1 0 1 1 0 1  
...

I have to find all of these uniqe combinations. In the example the answer must be 3, because the first row is the same as the last.
I can do it for one column, but when there's more of them, I have no idea, how should I do it?

Upvotes: 1

Views: 68

Answers (2)

CallumDA
CallumDA

Reputation: 12113

You can achieve this with VBA. Open the VBA editor and create a new module. Add this code to the module

Public Function CountUniqueRows(rng As Range) As Long
    Dim r As Range
    Dim s As String
    Dim dict As Object

    Set dict = CreateObject("scripting.Dictionary")

    With dict
        For Each r In rng.Rows
            s = Join(Application.Transpose(Application.Transpose(r.Value)), "")
            If Not .exists(s) Then
                .Add s, s
            End If
        Next r
    End With

    CountUniqueRows = dict.Count
End Function

You can then use the function on the worksheet like this which would return 3 as per your example

=CountUniqueRows(A1:F5)

Upvotes: 1

amcdermott
amcdermott

Reputation: 1585

Select your range of data.
On the Data ribbon, select the Remove Duplicates item in the Data Tools section.
Follow the instructions.
Voila!

If you want to keep the original data, you might want to copy and paste to a new sheet and remove the duplicates there.

Upvotes: 0

Related Questions