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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
510
asiuol84
A
M
  • Question Question
Generate 1X2 combinations by percentage.
Replies
9
Views
618
motilulla
M
M
  • Solved
VBA generate 5_50 combinations from (column A) list.
Replies
3
Views
384
StephenCrump
S
M
  • Solved
Generate combination using base line data.
Replies
1
Views
511
motilulla
M
S
  • Question Question
VBA: extract certain data from .txt file to new sheet
Replies
6
Views
853
st34lth
S

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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