If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Combinations listing

Thread starter rdev
Start date Jul 28, 2017
Tags

column combination entries items solution

R

rdev

Active Member

Jul 28, 2017

#1

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

Excel Facts

Will the fill handle fill 1, 2, 3?

Click here to reveal answer

Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Sort by date Sort by votes

My Aswer Is This

Well-known Member

Jul 28, 2017

#2

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

Last edited: Jul 28, 2017

Upvote 0

shg

MrExcel MVP

Jul 28, 2017

#3

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

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B

[/td][td="bgcolor:#C0C0C0"]

C

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2

[/td][td="bgcolor:#F3F3F3"]

n

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3

[/td][td="bgcolor:#E5E5E5"]

11

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4

[/td][td="bgcolor:#F3F3F3"]

mMin

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5

[/td][td]

2

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6

[/td][td="bgcolor:#F3F3F3"]

mMax

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7

[/td][td]

2

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8

[/td][td="bgcolor:#F3F3F3"]

nComb

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9

[/td][td="bgcolor:#E7E7E7"]

55

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10

[/td][td="bgcolor:#909090"][/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11

[/td][td="bgcolor:#F3F3F3"]

m

[/td][td]A B C D E F G H I J K[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12

[/td][td]

2

[/td][td]B A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13

[/td][td]

2

[/td][td]C A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14

[/td][td]

2

[/td][td]C B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15

[/td][td]

2

[/td][td]D A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16

[/td][td]

2

[/td][td]D B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17

[/td][td]

2

[/td][td]D C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18

[/td][td]

2

[/td][td]E A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19

[/td][td]

2

[/td][td]E B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20

[/td][td]

2

[/td][td]E C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21

[/td][td]

2

[/td][td]E D[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22

[/td][td]

2

[/td][td]F A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23

[/td][td]

2

[/td][td]F B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24

[/td][td]

2

[/td][td]F C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25

[/td][td]

2

[/td][td]F D[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26

[/td][td]

2

[/td][td]F E[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27

[/td][td]

2

[/td][td]G A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28

[/td][td]

2

[/td][td]G B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29

[/td][td]

2

[/td][td]G C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30

[/td][td]

2

[/td][td]G D[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31

[/td][td]

2

[/td][td]G E[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32

[/td][td]

2

[/td][td]G F[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33

[/td][td]

2

[/td][td]H A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34

[/td][td]

2

[/td][td]H B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35

[/td][td]

2

[/td][td]H C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36

[/td][td]

2

[/td][td]H D[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

37

[/td][td]

2

[/td][td]H E[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

38

[/td][td]

2

[/td][td]H F[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

39

[/td][td]

2

[/td][td]H G[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

40

[/td][td]

2

[/td][td]I A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

41

[/td][td]

2

[/td][td]I B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

42

[/td][td]

2

[/td][td]I C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

43

[/td][td]

2

[/td][td]I D[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

44

[/td][td]

2

[/td][td]I E[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

45

[/td][td]

2

[/td][td]I F[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

46

[/td][td]

2

[/td][td]I G[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

47

[/td][td]

2

[/td][td]I H[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

48

[/td][td]

2

[/td][td]J A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

49

[/td][td]

2

[/td][td]J B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

50

[/td][td]

2

[/td][td]J C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

51

[/td][td]

2

[/td][td]J D[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

52

[/td][td]

2

[/td][td]J E[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

53

[/td][td]

2

[/td][td]J F[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

54

[/td][td]

2

[/td][td]J G[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

55

[/td][td]

2

[/td][td]J H[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

56

[/td][td]

2

[/td][td]J I[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

57

[/td][td]

2

[/td][td]K A[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

58

[/td][td]

2

[/td][td]K B[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

59

[/td][td]

2

[/td][td]K C[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

60

[/td][td]

2

[/td][td]K D[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

61

[/td][td]

2

[/td][td]K E[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

62

[/td][td]

2

[/td][td]K F[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

63

[/td][td]

2

[/td][td]K G[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

64

[/td][td]

2

[/td][td]K H[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

65

[/td][td]

2

[/td][td]K I[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

66

[/td][td]

2

[/td][td]K J[/td][/tr]
[/table]

Upvote 0

R

rdev

Active Member

Jul 28, 2017

#4

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..

Upvote 0

M

MickG

MrExcel MVP

Jul 29, 2017

#5

Perhaps this for results in "B":-

Code:

[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]

Last edited: Jul 29, 2017

Upvote 0

R

rdev

Active Member

Jul 30, 2017

#6

MickG said:

Perhaps this for results in "B":-

Code:

[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

Upvote 0

M

MickG

MrExcel MVP

Jul 30, 2017

#7

Do you mean to show in sheet2 as in "New" below.
[TABLE="width: 116"]
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="class: xl63, width: 27, bgcolor: #DAEEF3"] [/TD]
[TD="class: xl63, width: 64, bgcolor: #DAEEF3"]A[/TD]
[TD="class: xl63, width: 64, bgcolor: #DAEEF3"]B[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]1[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]Original[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]New[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]2[/TD]
[TD="class: xl65, bgcolor: white"]A,B[/TD]
[TD="class: xl65, bgcolor: white"]B,A[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]3[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]A,C[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]C,A[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]4[/TD]
[TD="class: xl65, bgcolor: white"]A,D[/TD]
[TD="class: xl65, bgcolor: white"]D,A[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]5[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]A,E[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]E,A[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]6[/TD]
[TD="class: xl65, bgcolor: white"]B,C[/TD]
[TD="class: xl65, bgcolor: white"]C,B[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]7[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]B,D[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]D,B[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]8[/TD]
[TD="class: xl65, bgcolor: white"]B,E[/TD]
[TD="class: xl65, bgcolor: white"]E,B[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]9[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]C,D[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]D,C[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]10[/TD]
[TD="class: xl65, bgcolor: white"]C,E[/TD]
[TD="class: xl65, bgcolor: white"]E,C[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: #DAEEF3, align: right"]11[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]D,E[/TD]
[TD="class: xl64, bgcolor: #DCE6F1"]E,D[/TD]
[/TR]
</tbody>[/TABLE]

Upvote 0

R

rdev

Active Member

Jul 30, 2017

#8

Yes please

Upvote 0

M

MickG

MrExcel MVP

Jul 30, 2017

#9

Try this:-
Resuts sheet2 column "C"

Code:

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

Upvote 0

You must log in or register to reply here.

Similar threads

R

Solved

Combine two lists into rows and columns

Replies: 4

Views: 638

Aug 22, 2023

Joe4

S

Solved

Combine columns using dynamic array formula

Replies: 3

Views: 223

Aug 21, 2024

Fluff

D

Solved

How do I generate random numbers for duplicate entries in Column A

Replies: 8

Views: 557

Feb 29, 2024

Eric W

M

Solved

Index and Match

Replies: 3

Views: 133

Nov 19, 2024

Peter_SSs

V

Question

Filtered 2D array

Replies: 8

Views: 125

Dec 10, 2024

Cubist

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,224,823

Messages: 6,181,181

Members: 453,022

Latest member: Mohamed Magdi Tawfiq Emam

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top