Generating all possible combinations from a set of data and listing these in separate cells/columns.

J

jotun456

New Member
Joined
Mar 12, 2019
Messages
4
Hi everyone,

I'm looking for help with a solution to generate all possible combinations of a set of data, and have these combinations shown in separate columns.

Small example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]z[/TD]
[TD]&[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]z[/TD]
[TD]&[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]y[/TD]
[TD]&[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]x[/TD]
[TD]&[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD]2[/TD]
[TD]z[/TD]
[TD]&[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I will be working with larger datasets than in the above example, possibly generating 1-2000 combinations.
I am open to either a VBA or formula solution, but have very little experience with VBA so please provide some additional info rather than just code if possible.

Is it possible to do this?

Thanks :)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Based on your data starting "A1", Results start "F1"
NB:- Amend code for more columns !!
Code: 
[COLOR="Navy"]Sub[/COLOR] MG13Mar09
[COLOR="Navy"]Dim[/COLOR] n1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n3 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n4 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] n1 = 1 To Range("A" & Rows.Count).End(xlUp).Row
    [COLOR="Navy"]For[/COLOR] n2 = 1 To Range("B" & Rows.Count).End(xlUp).Row
        [COLOR="Navy"]For[/COLOR] n3 = 1 To Range("C" & Rows.Count).End(xlUp).Row
            [COLOR="Navy"]For[/COLOR] n4 = 1 To Range("D" & Rows.Count).End(xlUp).Row
            c = c + 1
            Cells(c, "F") = Cells(n1, "A")
            Cells(c, "G") = Cells(n2, "B")
            Cells(c, "H") = Cells(n3, "C")
            Cells(c, "I") = Cells(n4, "D")
            [COLOR="Navy"]Next[/COLOR] n4
        [COLOR="Navy"]Next[/COLOR] n3
  [COLOR="Navy"]Next[/COLOR] n2
[COLOR="Navy"]Next[/COLOR] n1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
using PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2[/td][td=bgcolor:#70AD47]Column1.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]z[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a[/td][td]
1​
[/td][td]y[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]x[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a[/td][td]
2​
[/td][td]z[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]y[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a[/td][td]
2​
[/td][td]x[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]z[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]
1​
[/td][td]y[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]x[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]
2​
[/td][td]z[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]y[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]
2​
[/td][td]x[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]z[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]c[/td][td]
1​
[/td][td]y[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]x[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]c[/td][td]
2​
[/td][td]z[/td][td]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]y[/td][td=bgcolor:#E2EFDA]&[/td][/tr]

[tr=bgcolor:#FFFFFF][td]c[/td][td]
2​
[/td][td]x[/td][td]&[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Query1,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Query2),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Column1.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Query3),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Column1"}, {"Column1.2"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom1", "Custom", each Query4),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Column1"}, {"Column1.3"})
in
    #"Expanded Custom2"[/SIZE]
 
Upvote 0
Thanks for the suggestions everyone - I am taking a look at the formula based method in appbox above and cannot get the formula to work. Could anyone help with this? I have filled in the top section as instructed and copied the formula from the example but all cells are just displaying 0.
 
Upvote 0
Unfortunately I am unable to use the HTML maker or add ins at work, I will have to look into this myself.

The issue is that the formula =INDEX(Symbols,MOD(INT((Line-1)*Cnt/Cum),Cnt)+1) in the https://app.box.com/s/47b28f19d794b25511be works within the downloaded file but not when I copy it into a new Excel spreadsheet.
 
Upvote 0
That's because it uses named ranges.

You could instead use that workbook and add what you need, or copy that sheet into your workbook.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Excel data from one sheet, copy cells from row i'm working on to excel 'form' based on cell value
Replies
2
Views
529
asiuol84
A
M
  • Question Question
Generate 1X2 combinations by percentage.
Replies
9
Views
643
motilulla
M
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
77
Peter_SSs
Peter_SSs
M
  • Solved
VBA generate 5_50 combinations from (column A) list.
Replies
3
Views
390
StephenCrump
S
M
  • Solved
Generate combination using base line data.
Replies
1
Views
534
motilulla
M

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top