Finding possible combinations.

[hkinwald]

I would like to know if it is possible to get a list of all combinations from Excel? I don't really know how to ask what I am looking for, so here is an example.

There is a list of names:

Joe
Jane
Bill
Dan
Mary
Chris

I want to know if it is possible to display the combinations of three of the names:

Joe, Jane, Bill
Joe, Jane, Dan
Joe, Jane, Mary
Dan, Mary, Chris
Jane, Bill, Dan
and so on

Any help would be appreciated. Thanks.

 

[Marcelo Branco]

Hi,

Welcome to the forum

Assuming your names in column A - header in row 1 and names beginning in row 2.

Try the macro below in a test-workbook
It cleans the contents of column C (so be careful when using it)
and displays the combinations in this column

Adjust the sheet-name accordingly (on the first code-line after the Dims)

Sub testComb()
    Dim i As Long, j As Long, k As Long, lastRow As Long, lin As Long
    Dim wk As Worksheet
 
    Set wk = Sheets("Plan1") '<---Change this accordindgly
 
    With wk
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("C1") = "Combinations"
        .Range("C2:C" & .Rows.Count).ClearContents
        lin = 1
 
        For i = 2 To lastRow - 2
            For j = i + 1 To lastRow - 1
                For k = j + 1 To lastRow
                    lin = lin + 1
                    .Range("C" & lin) = .Range("A" & i) & ", " & .Range("A" & j) & ", " & .Range("A" & k)
                Next k
            Next j
        Next i
 
    End With
 
End Sub

HTH

M.

 

[Marcelo Branco]

Complementing my previous post.

IMPORTANT
Remember that Excel 2007 or higher has 1,048,576 rows (i dont know/remember how many in earlier versions).

So, as the macro above is putting one combination below the other in column C you cannot have more than 185 names (1,038,220 combinations by 3)

M.

 

[hkinwald]

Thank you for all your help so far. I am having trouble with this part:

Set wk = Sheets("Plan1") '<---Change this accordindgly

I don't know what to put the name of the document, which is Crewing, or the sheet number. Sorry, I am not very good with macros. Also is that the only thing I have to change?

Thanks again.

 

[Marcelo Branco]

Hi,

Questions:
How many names do you have?
Where are they? Column and sheet name?

M.

 

[Sektor]

Sub TransposeData()

    Dim i As Integer, j As Integer, f As Integer, iUBound As Integer
    Dim arr As Variant, arrNew() As Variant
    
    arr = Range("A1", Range("A1").End(xlDown))
    iUBound = CInt(UBound(arr) / 3) + 1
    ReDim arrNew(1 To iUBound, 1 To 3)
    
    f = 1
    For i = 1 To UBound(arr, 1)
        j = j + 1
        arrNew(f, j) = arr(i, 1)
        If i Mod 3 = 0 Then
            j = 0
            f = f + 1
        End If
    Next
    
    Range("A3").Resize(UBound(arrNew, 1), 3) = arrNew

End Sub