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.

No	No	No	1	No	No	No	1			No	No	No	1	No	No	No	1
Yes	Yes	Yes	2	Yes	Yes	Yes	2			Yes	No	No	1	No	No	No	1
			3				3
			4				4
			5				5
			6				6
			7				7
			8				8
			9				9
			10				10

<tbody>
</tbody>

Thanks

Andrew

 

[Eric W]

Try:

Book1
	A	B	C							J	K	L	M						S	T	U	V	W	X
1	cat	car	pizza								cat	car	pizza							45	0	cat	car	pizza
2	dog	truck	pasta								cat	car	pasta								0	cat	car	pasta
3	zebra	cat	apple								cat	car	apple								0	cat	car	apple
4			cat								cat	car	cat								2	cat	car	hamburger
5			hamburger								cat	car	hamburger								0	cat	truck	pizza
6											cat	truck	pizza								0	cat	truck	pasta
7											cat	truck	pasta								0	cat	truck	apple
8											cat	truck	apple								0	cat	truck	hamburger
9											cat	truck	cat								2	dog	car	pizza
10											cat	truck	hamburger								0	dog	car	pasta
11											cat	cat	pizza								2	dog	car	apple
12											cat	cat	pasta								2	dog	car	cat
13											cat	cat	apple								2	dog	car	hamburger
14											cat	cat	cat								6	dog	truck	pizza
15											cat	cat	hamburger								2	dog	truck	pasta
16											dog	car	pizza								0	dog	truck	apple
17											dog	car	pasta								0	dog	truck	cat
18											dog	car	apple								0	dog	truck	hamburger
19											dog	car	cat								0	dog	cat	pizza
20											dog	car	hamburger								0	dog	cat	pasta
21											dog	truck	pizza								0	dog	cat	apple
22											dog	truck	pasta								0	dog	cat	hamburger
23											dog	truck	apple								0	zebra	car	pizza
24											dog	truck	cat								0	zebra	car	pasta
25											dog	truck	hamburger								0	zebra	car	apple
26											dog	cat	pizza								0	zebra	car	cat
27											dog	cat	pasta								0	zebra	car	hamburger
28											dog	cat	apple								0	zebra	truck	pizza
29											dog	cat	cat								2	zebra	truck	pasta
30											dog	cat	hamburger								0	zebra	truck	apple
31											zebra	car	pizza								0	zebra	truck	cat
32											zebra	car	pasta								0	zebra	truck	hamburger
33											zebra	car	apple								0	zebra	cat	pizza
34											zebra	car	cat								0	zebra	cat	pasta
35											zebra	car	hamburger								0	zebra	cat	apple
36											zebra	truck	pizza								0	zebra	cat	hamburger
37											zebra	truck	pasta								0
38											zebra	truck	apple								0
39											zebra	truck	cat								0
40											zebra	truck	hamburger								0
41											zebra	cat	pizza								0
42											zebra	cat	pasta								0
43											zebra	cat	apple								0
44											zebra	cat	cat								2
45											zebra	cat	hamburger								0
46																					1
47																					1
48																					1
49																					1
50																					1
Sheet5

Cell Formulas
Range	Formula
K1:M50	K1	=IF(ROW()>$T$1,"",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),""))
T1	T1	=PRODUCT(IFERROR(1/(1/(SUBTOTAL(3,OFFSET(A:A,0,COLUMN(A:H)-COLUMN(A:A))))),1))
U1:U50	U1	=SUM(IF(MUNIT(COLUMNS($A$1:$H$1))<>1,($K1:$R1=TRANSPOSE($K1:$R1))*($K1:$R1<>""),0))+(ROW()>$T$1)
V1:X50	V1	=IFERROR(INDEX(K:K,AGGREGATE(15,6,ROW(K$1:INDEX(K:K,$T$1))/($U$1:INDEX($U:$U,$T$1)=0),ROW())),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

T1 is just a formula to determine how many combinations there will be. I use it to determine a stopping condition for both the K1 and V1 formulas, as well as making the V1 formula more efficient. The U1 formula creates a helper column showing which rows have duplicates. Anything greater than 0 does. It'll also go above zero after it hits this maximum number of rows. It could be incorporated in the V1 formula, but you really don't want an array formula within an array formula. Then the V1 formula creates your list without the rows marked with a positive value in U.

See if this works for you.

 

[andrew_milonavic]

Hey @Eric W,

That's great, it works perfectly!!! I really appreciate it!

Thank you

Andrew

 

[CA_Punit]

Hi @Eric W

Could you please explain the first example. How did Row Number change from {1;1;2;2;1;1;2;2} to {1;1;1;1;2;2;2;2;1;1;1;1;2;2;2;2} as you move to next column.
I am not able to get strategy through evaluate formula option also

 

[Eric W]

Hi @Eric W

Could you please explain the first example. How did Row Number change from {1;1;2;2;1;1;2;2} to {1;1;1;1;2;2;2;2;1;1;1;1;2;2;2;2} as you move to next column.
I am not able to get strategy through evaluate formula option also

I'm not sure which example you're referring to. Which post number, and which cell number are you looking at?

 

[andrew_milonavic]

Hey @Eric W,

Is there a way to paste data into the data columns, in your example columns A-B-C, without the formula recognizing the blanks as duplicates?

Currently, if I paste your example data (A1:A5), the formula picks up the blanks from A4:B5 and then the results have blanks etc as they are being considered duplicates. Hope that makes sense?

Thoughts?

Thanks

Andrew

 

[Eric W]

Is there a way to paste data into the data columns, in your example columns A-B-C, without the formula recognizing the blanks as duplicates?

Currently, if I paste your example data (A1:A5), the formula picks up the blanks from A4:B5 and then the results have blanks etc as they are being considered duplicates. Hope that makes sense?

You're going to have to give me an example. There's a difference between an empty cell, and one with a space in it. The "duplicate" formula in U1 specifically ignores empty cells. Whenever I paste data in the A:H columns, I get the expected results, including spaces if they exist.

 

[andrew_milonavic]

Hey @Eric W,

Thanks for the fast reply!

I did a few tests. The problem appears to be because my data is compiled by an index match formula. Even though I have an IFNA and IF blank return blank in the statement apparently the cells still aren't blank.

Is there a work around for this? Or is my only option to copy and paste only the cells with data manually?

Thanks

Andrew

 

[Eric W]

Without seeing an example, I'd guess that the problem is with the SUBTOTAL (COUNTA) part of the formulas. COUNTA counts the number of "filled" cells in a range. It won't count an empty cell, but it will count a formula that returns an empty cell. There are ways to get around that, but it makes it tricky. Probably the easiest way would be to add 8 helper cells, each with the number of non-empty values in each of the A:H columns. You'd need to rearrange the layout in order to put them somewhere. I can also visualize another way using MMULT somehow (although I haven't worked out the details) where I could just update the existing formulas. But I'd be worried about performance.

Or like you said, you could copy/paste the values instead of using the INDEX/MATCH formula.

 

[andrew_milonavic]

Hey @Eric W,

I think I'll stick to copying it manually. It takes more time but you've already helped so much and removed all the heavy lifting that I don't mind some copy paste.

Thanks! Really appreciate the help

Andrew

 

[andrew_milonavic]

Hey @Eric W and Everyone,

It’s been awhile since I’ve been on Mr Excel, but I want to adjust a formula and can’t figure out how.

Previously Eric wrote this formula, and it works great, but I would like to add the functionality to prevent duplicates in the same row.

The original worked like this (Data on the left and combinations on the right).

Car1	Car1	Car1		Car1	Car2	Car3
Car2	Car2	Car2		Car1	Car3	Car2
Car3	Car3	Car3		Car2	Car1	Car3
				Car2	Car3	Car1
				Car3	Car1	Car2
				Car3	Car2	Car1

I would like it to work like this (Data on the left and combinations on the right):

Car1	Car1	Car1		Car1	Car2	Car3
Car2	Car2	Car2
Car3	Car3	Car3

I would like to stick to formulas, and prefer not to use VBA’s.


Eric's Original Formula:

K1:M50	K1	=IF(ROW()>$T$1,"",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),""))
T1	T1	=PRODUCT(IFERROR(1/(1/(SUBTOTAL(3,OFFSET(A:A,0,COLUMN(A:H)-COLUMN(A:A))))),1))
U1:U50	U1	=SUM(IF(MUNIT(COLUMNS($A$1:$H$1))<>1,($K1:$R1=TRANSPOSE($K1:$R1))*($K1:$R1<>""),0))+(ROW()>$T$1)
V1:X50	V1	=IFERROR(INDEX(K:K,AGGREGATE(15,6,ROW(K$1:INDEX(K:K,$T$1))/($U$1:INDEX($U:$U,$T$1)=0),ROW())),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Any help would be great!

Thanks

Andrew