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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
343
Excel Jason
E
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
601
asiuol84
A
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
475
Pingaware
P
M
  • Question Question
Generate 1X2 combinations by percentage.
Replies
9
Views
832
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
397
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,226,739
Messages
6,192,739
Members
453,755
Latest member
IQBS

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