Excel combinations from multiple columns with no duplicates

[andrew_milonavic]

Hi,

Is there a non VBA way to return all possible combinations without duplicates with the returned values in separate columns?

Example below. Data in the first 8 columns / 10 rows, results starting in the 11th column.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69, align: right"]1[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69, align: right"]1[/TD]
[TD="width: 69"][/TD]
[TD="width: 69"][/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69, align: right"]1[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69"]No[/TD]
[TD="width: 69, align: right"]1[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD="align: right"]1[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[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="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[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="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[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="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[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="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[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="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[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="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[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="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks

Andrew

 

[Eric W]

I'm not sure what you'd do with 256,000 combinations, but you can do it with formulas:

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	No	No	No	1	No	No	No	1			No	No	No	1	No	No	No	1
2	Yes	Yes	Yes	2	Yes	Yes	Yes	2			Yes	No	No	1	No	No	No	1
3				3				3			No	Yes	No	1	No	No	No	1
4				4				4			Yes	Yes	No	1	No	No	No	1
5				5				5			No	No	Yes	1	No	No	No	1
6				6				6			Yes	No	Yes	1	No	No	No	1
7				7				7			No	Yes	Yes	1	No	No	No	1
8				8				8			Yes	Yes	Yes	1	No	No	No	1
9				9				9			No	No	No	2	No	No	No	1
10				10				10			Yes	No	No	2	No	No	No	1
11											No	Yes	No	2	No	No	No	1
12											Yes	Yes	No	2	No	No	No	1
13											No	No	Yes	2	No	No	No	1
14											Yes	No	Yes	2	No	No	No	1
15											No	Yes	Yes	2	No	No	No	1
16											Yes	Yes	Yes	2	No	No	No	1
17											No	No	No	3	No	No	No	1
18											Yes	No	No	3	No	No	No	1
19											No	Yes	No	3	No	No	No	1
20											Yes	Yes	No	3	No	No	No	1
Sheet2

Cell Formulas
Range	Formula
K1:K20	K1	=INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1)
L1:R20	L1	=INDEX(B:B,MOD(INT((ROW()-1)/PRODUCT(SUBTOTAL(3,OFFSET($A:$A,0,COLUMN($K1:K1)-COLUMN($K1))))),COUNTA(B:B))+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Put the K1 formula in and drag down as far as needed. Put the L1 formula in and drag down and to the right as far as needed. It's an array formula, so confirm with Control+Shift+Enter.


If you are OK with using some helper cells, you can simplify the formulas quite a bit and not need the array processing.

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	No	No	No	1	No	No	No	1			No	No	No	1	No	No	No	1	2	2	2	10	2	2	2	10
2	Yes	Yes	Yes	2	Yes	Yes	Yes	2			Yes	No	No	1	No	No	No	1	1	2	4	8	80	160	320	640
3				3				3			No	Yes	No	1	No	No	No	1
4				4				4			Yes	Yes	No	1	No	No	No	1
5				5				5			No	No	Yes	1	No	No	No	1
6				6				6			Yes	No	Yes	1	No	No	No	1
7				7				7			No	Yes	Yes	1	No	No	No	1
8				8				8			Yes	Yes	Yes	1	No	No	No	1
9				9				9			No	No	No	2	No	No	No	1
10				10				10			Yes	No	No	2	No	No	No	1
11											No	Yes	No	2	No	No	No	1
12											Yes	Yes	No	2	No	No	No	1
13											No	No	Yes	2	No	No	No	1
14											Yes	No	Yes	2	No	No	No	1
15											No	Yes	Yes	2	No	No	No	1
16											Yes	Yes	Yes	2	No	No	No	1
Sheet1

Cell Formulas
Range	Formula
K1:R16	K1	=INDEX(A:A,MOD(INT((ROW()-1)/S$2),S$1)+1)
S1:Z1	S1	=COUNTA(A:A)
T2:Z2	T2	=S2*COUNTA(A:A)

Put the S1 formula in and drag to Z1. Put 1 in S2. Put the T2 formula in and drag to Z2. Then put the K1 formula in, and copy down and across as far as needed.

 

[andrew_milonavic]

Hey Eric,

Thank you!!! Both options work. Regarding the data, it's to test a filtering plugin.

Thanks

Andrew

 

[andrew_milonavic]

Hey @Eric W or Anyone else,

Is there a way to edit Eric's formulas from above (the one without the helper column) and change it so that the results order is different? Currently it goes line by line and I'd rather it output like this three column example below. Possible?

Results are on the right.

Cat	Car	Pizza		Cat	Car	Pizza
Dog	Truck	Pasta		Cat	Car	Pasta
Zebra	Van	Apple		Cat	Car	Apple
Mouse	Plane	Orange		Cat	Car	Orange
Elephant	Boat	Rice		Cat	Car	Rice

Eric's Formulas:

=INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1)

=INDEX(B:B,MOD(INT((ROW()-1)/PRODUCT(SUBTOTAL(3,OFFSET($A:$A,0,COLUMN($K1:K1)-COLUMN($K1))))),COUNTA(B:B))+1)


Thanks

Andrew

 

[Eric W]

How about:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	cat	car	pizza								cat	car	pizza
2	dog	truck	pasta								cat	car	pasta
3	zebra	van	apple								cat	car	apple
4	mouse	plane	orange								cat	car	orange
5	elephant	boat									cat	truck	pizza
6											cat	truck	pasta
7											cat	truck	apple
8											cat	truck	orange
9											cat	van	pizza
10											cat	van	pasta
11											cat	van	apple
12											cat	van	orange
13											cat	plane	pizza
14											cat	plane	pasta
15											cat	plane	apple
16											cat	plane	orange
17											cat	boat	pizza
18											cat	boat	pasta
19											cat	boat	apple
20											cat	boat	orange
21											dog	car	pizza
22											dog	car	pasta
23											dog	car	apple
24											dog	car	orange
25											dog	truck	pizza
26											dog	truck	pasta
27											dog	truck	apple
28											dog	truck	orange
29											dog	van	pizza
30											dog	van	pasta
31											dog	van	apple
32											dog	van	orange
33											dog	plane	pizza
Sheet15

Cell Formulas
Range	Formula
K1:R33	K1	=IFERROR(INDEX(A:A,MOD(INT((ROW()-1)/PRODUCT(IFERROR(1/(1/SUBTOTAL(3,OFFSET(B:B,0,COLUMN(B:$I)-COLUMN(B:B)))),1))),COUNTA(A:A))+1),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

I took the rice off just to test columns of different sizes. This works out to column H/R if you want to add additional columns. There's no stopping condition, so after 5*5*4 rows, it'll just start to repeat itself, but I can add that if you want.

 

[andrew_milonavic]

Hey @Eric W ,

That's awesome! Works perfectly.

Thank you, greatly appreciated!

Andrew

 

[Eric W]

Glad to help!

 

[andrew_milonavic]

Hey @Eric W or Anyone else,

Is there a way to adjust Eric's formula (see post above and formula below) to stop duplicates? i.e Cat Cat, if Cat is in the data of both columns A and B.

Eric's Formula: IFERROR(INDEX(A:A,MOD(INT((ROW()-1)/PRODUCT(IFERROR(1/(1/SUBTOTAL(3,OFFSET(B:B,0,COLUMN(B:$I)-COLUMN(B:B)))),1))),COUNTA(A:A))+1),"")

Thanks

Andrew

 

[Eric W]

You've about exceeded what you can reasonably do with formulas. Telling it to skip a row throws off the counters for everything after that row. The only non-VBA way to do this I can think of would be to set up the formulas as is in one set of columns, then in another set of columns set up another array formula that would skip the rows with duplicates. And I assume that a duplicate means the same value in more than one column, no matter how many columns you have?

 

[andrew_milonavic]

Hey @Eric W

Thanks for the reply!

Your idea would work. Is it easy to configure?

Correct. If Cat is in three columns I want only one to appear.

Thanks

Andrew