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?

 

[mikiel111]

.....




Well done! That's good to hear.

What if in sheet daily meal macro I want to put stuff above the rows i`m using or I want to delete the A column. So for example my first entries would go in row 4 not 2 and would shift to from Column C to B, and E to D etc...
Same with the other sheet Meal List.

 

[StephenCrump]

We have defined two worksheet scoped names: StartRow, pointing to:
='Meal List''!$A$10 (the start of my element list), and
='Daily Meal Macro'!$C$2 (the first output cell for the combinations).

This is how the code uses these names:

'Input - get the elements starting from 'Meal List'!C10
With Worksheets("Meal List")
    Set rng = .Range("A" & .Range("StartRow").Row & ":A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With

'...
        
'Output - write to alternate columns starting at 'Daily Meal Macro'!C2
With Worksheets("Daily Meal Macro").Range("StartRow")
    '....
End With

If, for example, you insert two rows at the top of 'Daily Meal Macro', StartRow will adjust automatically to 'Daily Meal Macro'!$C$4, and the code will put the output in the correct place.

 

[mikiel111]

Much much ... MUCH appreciated! I`m going through it as currently super busy!