Combinations listing

[rdev]

I have the following in column A starting at Row 1:
A
B
C
D
E
F

I want the combination AB,AC,AD, AE,AF , BC,BD,BE,BF,CD,CE,CF,DE,DF,EF to appear in column B . I have no clue using VBA to solve this . Column A can have as much as 200 entries and therefore the solution should work for all possible combination of those 200 items
Thank you

 

[My Aswer Is This]

You showed us a example of what you have in column "A"

Please show us how you want things to look after script runs.

I do not understand:

I want the combination AB,AC,AD, AE,AF , BC,BD,BE,BF,CD,CE,CF,DE,DF,EF

 

[shg]

There's a workbook at https://app.box.com/s/b9b9fc06beb63b9562f9 that will do this:

	B​	C​
2​	n​
3​	11​
4​	mMin​
5​	2 ​
6​	mMax​
7​	2 ​
8​	nComb​
9​	55​
10​
11​	m​	A B C D E F G H I J K
12​	2​	B A
13​	2​	C A
14​	2​	C B
15​	2​	D A
16​	2​	D B
17​	2​	D C
18​	2​	E A
19​	2​	E B
20​	2​	E C
21​	2​	E D
22​	2​	F A
23​	2​	F B
24​	2​	F C
25​	2​	F D
26​	2​	F E
27​	2​	G A
28​	2​	G B
29​	2​	G C
30​	2​	G D
31​	2​	G E
32​	2​	G F
33​	2​	H A
34​	2​	H B
35​	2​	H C
36​	2​	H D
37​	2​	H E
38​	2​	H F
39​	2​	H G
40​	2​	I A
41​	2​	I B
42​	2​	I C
43​	2​	I D
44​	2​	I E
45​	2​	I F
46​	2​	I G
47​	2​	I H
48​	2​	J A
49​	2​	J B
50​	2​	J C
51​	2​	J D
52​	2​	J E
53​	2​	J F
54​	2​	J G
55​	2​	J H
56​	2​	J I
57​	2​	K A
58​	2​	K B
59​	2​	K C
60​	2​	K D
61​	2​	K E
62​	2​	K F
63​	2​	K G
64​	2​	K H
65​	2​	K I
66​	2​	K J

 

[rdev]

First letter in Column A concatenates with the one below it , when the macro reaches the last row , then it starts concatenating as from row 2 to last row in the column
then row 3 to last row in column etc..

 

[MickG]

Perhaps this for results in "B":-

[COLOR=navy]Sub[/COLOR] Combinations()
'Ref:- PGC1
[COLOR=navy]Dim[/COLOR] rRng [COLOR=navy]As[/COLOR] Range, p [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] vElements, lRow [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] vresult [COLOR=navy]As[/COLOR] Variant
 [COLOR=navy]Set[/COLOR] rRng = Range("A1", Range("A1").End(xlDown))
p = 2
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 
[COLOR=navy]Sub[/COLOR] CombinationsNP(vElements [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] vresult [COLOR=navy]As[/COLOR] Variant, lRow [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] iElement [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] iIndex [COLOR=navy]As[/COLOR] Integer)
[COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
 
[COLOR=navy]For[/COLOR] i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    [COLOR=navy]If[/COLOR] iIndex = p [COLOR=navy]Then[/COLOR]
        lRow = lRow + 1
        Range("B" & lRow).Value = Join(vresult, ",")
    [COLOR=navy]Else[/COLOR]
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] i
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

 

[rdev]

Perhaps this for results in "B":-

[COLOR=navy]Sub[/COLOR] Combinations()
'Ref:- PGC1
[COLOR=navy]Dim[/COLOR] rRng [COLOR=navy]As[/COLOR] Range, p [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] vElements, lRow [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] vresult [COLOR=navy]As[/COLOR] Variant
 [COLOR=navy]Set[/COLOR] rRng = Range("A1", Range("A1").End(xlDown))
p = 2
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 
[COLOR=navy]Sub[/COLOR] CombinationsNP(vElements [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] vresult [COLOR=navy]As[/COLOR] Variant, lRow [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] iElement [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] iIndex [COLOR=navy]As[/COLOR] Integer)
[COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
 
[COLOR=navy]For[/COLOR] i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    [COLOR=navy]If[/COLOR] iIndex = p [COLOR=navy]Then[/COLOR]
        lRow = lRow + 1
        Range("B" & lRow).Value = Join(vresult, ",")
    [COLOR=navy]Else[/COLOR]
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] i
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Thanks , this is great, I concede this is too much for me to understand, I am trying .How now we can change the order to read in BA,CA etc in sheet 2 as from cell A1

 

[MickG]

Do you mean to show in sheet2 as in "New" below.

	A	B
1	Original	New
2	A,B	B,A
3	A,C	C,A
4	A,D	D,A
5	A,E	E,A
6	B,C	C,B
7	B,D	D,B
8	B,E	E,B
9	C,D	D,C
10	C,E	E,C
11	D,E	E,D

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

 

[rdev]

Yes please

 

[MickG]

Try this:-
Resuts sheet2 column "C"

Sub Combinations()
Dim rRng As Range, p As Integer
Dim vElements, lRow As Long, vresult As Variant
 Set rRng = Range("A1", Range("A1").End(xlDown))
p = 2
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
End Sub
 
Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Integer
 
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        lRow = lRow + 1
        'New line below
        Sheets("Sheet2").Range("C" & lRow).Value = vresult(2) & "," & vresult(1)
    Else
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    End If
Next i
End Sub