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

[jotun456]

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:

a	1	z	&		a	1	z	&
b	2	y			a	1	y	&
c		x			a	1	x	&
					a	2	z	&
					etc	etc	etc	etc

<tbody>
</tbody>

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

 

[Special-K99]

"Larger datasets"

Does that mean more columns or just more rows ?

 

[MickG]

Based on your data starting "A1", Results start "F1"
NB:- Amend code for more columns !!

[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

 

[sandy666]

using PowerQuery

Column1	Column1.1	Column1.2	Column1.3
a	1​	z	&
a	1​	y	&
a	1​	x	&
a	2​	z	&
a	2​	y	&
a	2​	x	&
b	1​	z	&
b	1​	y	&
b	1​	x	&
b	2​	z	&
b	2​	y	&
b	2​	x	&
c	1​	z	&
c	1​	y	&
c	1​	x	&
c	2​	z	&
c	2​	y	&
c	2​	x	&

[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]

 

[shg]

There's a workbook with both formula- and vba-based methods at https://app.box.com/s/47b28f19d794b25511be

 

[jotun456]

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.

 

[shg]

Post the workbook.

 

[jotun456]

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.

 

[shg]

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.

 

[jotun456]

Ok, I will copy the sheet into a new workbook. Thanks a lot for your help