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

What did Pito Salas invent?

Click here to reveal answer

Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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: 631

Aug 22, 2023

Joe4

S

Solved

Combine columns using dynamic array formula

Replies: 3

Views: 208

Aug 21, 2024

Fluff

D

Solved

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

Replies: 8

Views: 542

Feb 29, 2024

Eric W

Question

I want to return a list of headers wherever there is a value match in column N. Where TRUE exists in any row of a column, that header is returned plus

Replies: 1

Views: 1K

Apr 19, 2023

Wad Mabbit

Question

Worksheet expression to select items from a list using weighted random numbers

Replies: 2

Views: 500

Oct 19, 2023

JenniferMurphy

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,223,903

Messages: 6,175,284

Members: 452,630

Latest member: OdubiYouth

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top