unique combinations generator

[mikiel111]

Hi there,

I`ve got this issue which i just cannot seem to get to the bottom of.
I need a unique combination generator from entries in 1 column. To elaborate, I have 1 column with any number of rows (A, B, C, D etc...) in sheet A & I need to output unique combinations of all the entries from that column in groups of 5 in Sheet B
So let`s say I have Column 1 with A, B, C, D & E in the rows. I need to output the unique combinations of those (e.g. AAAAA AAAAB AAAAC, AAAAD, AAABC, AAABD etc...). The order of the letters is not important, so tby that I mean if I have AAAAB I dont want AAABA, AABAA, ABAAA & BAAAA.
I found an excel file on the internet which does what seems like I need it to do but the knowledge behind it is waaaaayy beyond me (hence why i`m reaching out for help) so I cannot copy it, see whats happening or recreate it in my own file.

Is there some way I can attach it so you see what I am talking about?

 

[DanteAmor]

Hi and welcome to MrExcel.

Just to understand how you have your data.

They are like this:

varios
	A
1	AAABA
2	AAAAB
3	BAAAA
4	AABAA
Sheet A

And you want this result:

varios
	A
1	AAAAB
Sheet B

How many records do you have on sheet A?

 

[StephenCrump]

Welcome to the Forum!

Workbook here: Box

Using general code (thanks pgc01) for combinations/permutations with/without repetition here: Combination, Repeats (AAB,BBC) allowed, Repeats (AAC,ACA,CAA) only considered as One

Results:

Combinations.xlsm
	A	B	C	D	E	F	G	H
1	A	5		A	A	A	A	A
2	B			A	A	A	A	B
3	C			A	A	A	A	C
4	D			A	A	A	A	D
5	E			A	A	A	A	E
6				A	A	A	B	B
7				A	A	A	B	C
8				A	A	A	B	D
9				A	A	A	B	E
10				A	A	A	C	C
11				A	A	A	C	D
12				A	A	A	C	E
13				A	A	A	D	D
14				A	A	A	D	E
15				A	A	A	E	E
16				A	A	B	B	B
17				A	A	B	B	C
18				A	A	B	B	D
19				A	A	B	B	E
20				A	A	B	C	C
21				A	A	B	C	D
22				A	A	B	C	E
23				A	A	B	D	D
24				A	A	B	D	E
25				A	A	B	E	E
26				A	A	C	C	C
27				etc
Sheet1

 

[mikiel111]

Welcome to the Forum!

Workbook here: Box

Using general code (thanks pgc01) for combinations/permutations with/without repetition here: Combination, Repeats (AAB,BBC) allowed, Repeats (AAC,ACA,CAA) only considered as One

Results:

Combinations.xlsm
	A	B	C	D	E	F	G	H
1	A	5		A	A	A	A	A
2	B			A	A	A	A	B
3	C			A	A	A	A	C
4	D			A	A	A	A	D
5	E			A	A	A	A	E
6				A	A	A	B	B
7				A	A	A	B	C
8				A	A	A	B	D
9				A	A	A	B	E
10				A	A	A	C	C
11				A	A	A	C	D
12				A	A	A	C	E
13				A	A	A	D	D
14				A	A	A	D	E
15				A	A	A	E	E
16				A	A	B	B	B
17				A	A	B	B	C
18				A	A	B	B	D
19				A	A	B	B	E
20				A	A	B	C	C
21				A	A	B	C	D
22				A	A	B	C	E
23				A	A	B	D	D
24				A	A	B	D	E
25				A	A	B	E	E
26				A	A	C	C	C
27				etc
Sheet1

I`m doing my best to stay away from code as I know nothing about coding and if i want even the smallest thing changed i need to stay bugging people to help me.
I will explore however this. In the link you posted do I need to look at post #6?

 

[mikiel111]

Hi and welcome to MrExcel.

Just to understand how you have your data.

They are like this:

varios
	A
1	AAABA
2	AAAAB
3	BAAAA
4	AABAA
Sheet A

And you want this result:

varios
	A
1	AAAAB
Sheet B

How many records do you have on sheet A?

My data is the single column on the far right (entries A, B, C etc..) and I wanted the final output as shown int he coloured columns (so in column, B, D, F, H & J)

 

[mikiel111]

I`m doing my best to stay away from code as I know nothing about coding and if i want even the smallest thing changed i need to stay bugging people to help me.
I will explore however this. In the link you posted do I need to look at post #6?

Do I just copy paste his VBA code and make it into a macro? I mean it`s like you have someone that needs to fly to another country and you`ve given me a plane and said ... here fly it..... errrr i`m not a pilot

 

[mikiel111]

Ok, I think i`m getting somewhere. I have copied the module from your workbook stephencrump to my excel sheet. However I have my list of items in Column B starting at cell 10 (this is in sheet A) and I need my output in sheet B in columns D, F, H, J, L (starting from row 2). (so to live nothing to the imagination first output in D2, F2, H2, J2, L2, next output in D3, F3 etc....

What should i change from below to achieve the above?

' PGC Set 2007
' Calculates and writes the Combinations / Permutations with/without repetition
' vElements - Array with the set elements (1 to n)
' p - number of elements in 1 combination/permutation
' bComb - True: Combinations, False: Permutations
' bRepet - True: with repetition, False: without repetition
' vResult - Array to hold 1 permutation/combination (1 to p)
' lRow - row number. the next combination/permutation is written in lRow+1
' vResultAll - Array to hold all the permutations/combinations (1 to Total, 1 to p)
' iElement - order of the element to process in case of combination
' iIndex - position of the next element in the combination/permutation
' Sub CombPerm() deals with the input / output
' Sub CombPermNP() generates the combinations / permutations
Sub Go()

    Dim vElements As Variant, vResult As Variant, vResultAll As Variant
    Dim lrow As Long, lTotal As Long, p As Long
    Dim bComb As Boolean, bRepet As Boolean
    Dim rng As Range
    
    p = Range("N").Value
    bComb = True
    bRepet = True
    Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    vElements = Application.Index(Application.Transpose(rng), 1, 0)
    
    With Application
        If bComb Then
            lTotal = .Combin(UBound(vElements) + IIf(bRepet, p - 1, 0), p)
        Else
            If bRepet = False Then lTotal = .Permut(UBound(vElements), p) Else lTotal = UBound(vElements) ^ p
        End If
    End With
    ReDim vResult(1 To p)
    ReDim vResultAll(1 To lTotal, 1 To p)

    Call CombPermNP(vElements, p, True, True, vResult, lrow, vResultAll, 1, 1)
    
    With Range("D1")
        .CurrentRegion.ClearContents
        .Resize(lTotal, p).Value = vResultAll
    End With
    
End Sub
Sub CombPermNP(ByVal vElements As Variant, ByVal p As Integer, ByVal bComb As Boolean, ByVal bRepet As Boolean, _
                             ByVal vResult As Variant, ByRef lrow As Long, ByRef vResultAll As Variant, ByVal iElement As Integer, ByVal iIndex As Integer)
Dim i As Integer, j As Integer, bSkip As Boolean
 
For i = IIf(bComb, iElement, 1) To UBound(vElements)
    bSkip = False
    ' in case of permutation without repetition makes sure the element is not yet used
    If (Not bComb) And Not bRepet Then
        For j = 1 To p
            If vElements(i) = vResult(j) And Not IsEmpty(vResult(j)) Then
                bSkip = True
                Exit For
            End If
        Next
    End If
 
    If Not bSkip Then
        vResult(iIndex) = vElements(i)
        If iIndex = p Then
            lrow = lrow + 1
            For j = 1 To p
                vResultAll(lrow, j) = vResult(j)
            Next j
        Else
            Call CombPermNP(vElements, p, bComb, bRepet, vResult, lrow, vResultAll, i + IIf(bComb And bRepet, 0, 1), iIndex + 1)
        End If
    End If
Next i
End Sub

 

[mikiel111]

I may also sometimes have to create combinations which are less than 4. Is this achievable with that code?

Also sorry for the millions of psots, i`m used to forum where i can edit my posts

 

[StephenCrump]

I'm happy to make the changes to the code, and I will point you to the relevant code lines so that you can understand which lines you might want to change subsequently. But let's be clear about the specifications, so I only need to do it once:

- Is the code generating the results you want (combinations with repetition)?

- List of items starting in 'Sheet A'B10? (You said M2 in Post #5?) Can the code assume there will be nothing in all cells below the list?

- Output starting in 'Sheet A' D2, F2, H2, J2, L2. (In Post #5, you said column, B, D, F, H & J?) Can I assume no used cells below these output columns?

The code will generate combination of N, where N is 1,2,3,4,5 ... Is 5 the maximum number you will need. Or should we allow for more columns N2, P2 ...

 

[mikiel111]

Thank you very much! I reviewed my excel sheet layout & changed the columns. Instead of working with strings I think it`s better we work with their IDs, simple numbers. If it is not easier tell me so I`ll change stuff around.

- The sheet you shared with me outputs the exact results that I want (so if i have AAAAB, I dont want AAABA, AABAA, ABAAA or BAAAA).

- My list of items (source data) starts in a sheet called Meal List. The first entry will be in A10, second entry in A11, third in A12 etc... These are the IDs for my entries which will be held adjcent in column B (so A10 has the description in B10). The IDs (again held in the A column) are as simple as can be; first ID is 1, second ID is 2 etc... Yes, the code can assume there is nothing below the list. Just in case it is important to know in the same row there things related to that same entry (the description in column B for example). Above the list there will be things and I may need to shift the rows down to add stuff. So if my data started from row 10 now it starts from row 11 as I would have added a row above for whatever reason.

- The output is in a different sheet called Daily Meal Macro. Yes you can assume no used cells below the output however once again i may put stuff above it. The output columns are C, E, G, I & K. If we make 6 combinations then the 6th column would be M. The output should start from second row -> C2, E2, G2, I2, K2 (& M2 if we can go up to 6 combinations)

- 5 is sufficient, if it is no hassle to make it take up to 6 that would be great but it`s a "nice to have" not a "must have". I`m highly appreciative of what youre doing

So basically the combinations outputted will be numbers (11111, 11112, 11113 etc....), like that I can use match/index or lookups to load corresponding data. Does this make it easier?
Is it OK to have the option to add/remove stuff (hence rows) above the input/output rows?