Multiple combinations

[rdev]

Say I have the following in Column A
A
B
C
D
E
The number of rows in column A can be variable,
With 5 values in column A, I can have 10 combinations like AB,AC,AD, AE,BC, BD,BE,CD,CE,DE
I want to list list the combinations in column B , I am having problem with the loop, I used concatenation
Any help would be appreciated

 

[Beyond_avarice]

Your examples seem to indicate you want a "Permutation" (AE is not equal to EA as order matters) instead of a "Combination"(AE is the same EA as order doesn't matter).

Sub permu()

Dim lRow As Long

lRow = Cells(Rows.Count, 1).End(xlUp).Row
    z = 0

    For i = 1 To lRow
        
        For o = 1 To lRow

            If Range("A" & i).Value = Range("A" & o).Value Then
                
                Else: Range("B" & i).Offset(z).Value = Range("A" & i).Value & Range("A" & o).Value
                      z = z + 1
            End If

        Next o
        z = z - 1
    
    Next i
     
 
End Sub

 

[JLGWhiz]

Or

Sub t()
With ActiveSheet
For i = 1 To 5
    For j = i + 1 To 5
        .Cells(Rows.Count, 2).End(xlUp)(2) = .Cells(i, 1).Value & .Cells(j, 1).Value
    Next
Next
End With
End Sub

If data begins in A1

 

[johnmpl]

Hi, to all!

If your data is in column A begins in A1, and have variable data, and you want the results in column B:

Sub Result()    
    Dim a&, Data, i&, j&, d&
    
    a = Application.CountA(Range("A:A"))
    ReDim Res(1 To Evaluate("=COMBIN(" & a & ", 2)"))
    Data = Range("A1", Range("A" & Rows.Count).End(xlUp))
    For i = 1 To a - 1
        For j = i + 1 To a
            d = d + 1: Res(d) = Data(i, 1) & Data(j, 1)
        Next j
    Next i
       
    Range("B:B").ClearContents
    Range("B1").Resize(d) = Application.Transpose(Res)
    Erase Data, Res
End Sub

Blessings!

 

[bsquad]

I think what you are looking for is something like

[B]Excel 2013 32 bit[/B]
[SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH][CENTER][COLOR=#FFFFFF]A[/COLOR][/CENTER]
[/TH]
[TH][CENTER][COLOR=#FFFFFF]B[/COLOR][/CENTER]
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]1[/B][/COLOR][/CENTER]
[/TD]
[TD]A[/TD]
[TD][RIGHT]=COMBIN([COLOR=#0000FF]COUNTA([COLOR=#FF0000]A1:A10[/COLOR]),2[/COLOR])[/RIGHT]
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]2[/B][/COLOR][/CENTER]
[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]3[/B][/COLOR][/CENTER]
[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]4[/B][/COLOR][/CENTER]
[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]5[/B][/COLOR][/CENTER]
[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]6[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]7[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]8[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]9[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"][CENTER][COLOR=#FFFFFF][B]10[/B][/COLOR][/CENTER]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE][TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: [B]Sheet2[/B][/TD]
[/TR]
</tbody>[/TABLE]

 

[bsquad]

Sorry, I totally misread your question;

try this formula in B1, and drag down.


=IF(ROW()-ROW($B$1)+1>COUNTA($A$1:$A$6)*COUNTA($A$1:$A$6),"",INDEX($A$1:$A$6,INT((ROW()-ROW($B$1))/COUNTA($A$1:$A$6)+1))&INDEX($A$1:$A$6,MOD(ROW()-ROW($B$1),COUNTA($A$1:$A$6))+1))