How to find all possible combinations of 2 identical sets of data without duplicates?

Welcome to the board.

You could use the workbook at https://app.box.com/s/47b28f19d794b25511be to generate the Cartesian product via formulas or VBA, and then filter to get the 36 distinct results:

	B	C	D	E	F	G
7					Filter
18	Yellow	Pink	Blue	Brown	1	F18: =--(SUMPRODUCT(1/COUNTIF(B18:E18,B18:E18)) = 4)
19	Yellow	Pink	Blue	Black	1
20	Yellow	Pink	Brown	Blue	1
22	Yellow	Pink	Brown	Black	1
23	Yellow	Pink	Black	Blue	1
24	Yellow	Pink	Black	Brown	1
27	Yellow	White	Blue	Brown	1
28	Yellow	White	Blue	Black	1
29	Yellow	White	Brown	Blue	1
31	Yellow	White	Brown	Black	1
32	Yellow	White	Black	Blue	1
33	Yellow	White	Black	Brown	1
36	Pink	Yellow	Blue	Brown	1
37	Pink	Yellow	Blue	Black	1
38	Pink	Yellow	Brown	Blue	1
40	Pink	Yellow	Brown	Black	1
41	Pink	Yellow	Black	Blue	1
42	Pink	Yellow	Black	Brown	1
54	Pink	White	Blue	Brown	1
55	Pink	White	Blue	Black	1
56	Pink	White	Brown	Blue	1
58	Pink	White	Brown	Black	1
59	Pink	White	Black	Blue	1
60	Pink	White	Black	Brown	1
63	White	Yellow	Blue	Brown	1
64	White	Yellow	Blue	Black	1
65	White	Yellow	Brown	Blue	1
67	White	Yellow	Brown	Black	1
68	White	Yellow	Black	Blue	1
69	White	Yellow	Black	Brown	1
72	White	Pink	Blue	Brown	1
73	White	Pink	Blue	Black	1
74	White	Pink	Brown	Blue	1
76	White	Pink	Brown	Black	1
77	White	Pink	Black	Blue	1
78	White	Pink	Black	Brown	1

Cross-posted at: http://www.msofficeforums.com/excel/...identical.html

Please read the Cross-Posting policy in rule #8: http://www.excelforum.com/forum-rule...rum-rules.html

Originally Posted by Jmmcintosh21

But the other place I couldn't find an answer

Without wanting to place too fine a point on it, the gap of a mere 6 minutes between starting the two threads hardly justifies your assertion that the lack of response there motivated posting here.

Originally Posted by Jmmcintosh21

so I'm trying to find it here.

And that doesn't exempt you from the forum rules...

You filter for 1.

If there are more or fewer than 4 columns in the table, the 4 in the formula needs to be changed to agree.

Hmm, i'm still running into a bit of confusion. I tried to post an image of what I have so far. I'm confused on how you get that filter column to even appear and even more confused on how you added that '1' to it. :S Thanks for your help so far.

exampleex.png

I generated the arrangments, and then added the formula in a convenient column. See attached.

Dude your a beast. I understand it now. Thanks so much. This may be a little much since its not the original question but I didn't realize that the same lines could appear in a different order.

For example:
yellow pink blue brown
pink yellow brown blue

Do you have any ideas to be able to pinpoint all of these. Maybe sorting or conditional formatting. Its ok if you don't know I was just curious. Thanks for all your help again. Really appreciate it.

So all you want is combinations of different colors? That's a totally different animal than a Cartesian product.

Please think before answering.