Exclusive combination for 3 rows

[cbprado]

Hello,

Looking for a formula to generate all possible UNIQUE combination of the contents of 3 rows. I want to avoid results with the same content in different positions (such as aac, caa, aca should be appear only once, regardless of which)

Row1 Row2 Row3
a a a
b b b
c c c


anyone?

 

[jim may]

Given your 3 row example -- what output would you expect (please provide) from the formula?

 

[sandy666]

is that what you want?

result

a a a

a a b

a a c

a b a

a b b

a b c

a c a

a c b

a c c

b a a

b a b

b a c

b b a

b b b

b b c

b c a

b c b

b c c

c a a

c a b

c a c

c b a

c b b

c b c

c c a

c c b

c c c

 

[cbprado]

This is pretty much it, which I achieved with iferror.. what I need now is that the results supress combinations that are the same, but in different positions. Tha t to say that for the results below for instance, aac, aca, caa should appear only once (regardless of each one it is).
Imagine rolling 3 dices, if I get 1 2 1 or 2 1 1 or 1 1 2 in practical terms it´s the same result. this is where I need to get to ...

is that what you want?

result

a a a

a a b

a a c

a b a

a b b

a b c

a c a

a c b

a c c

b a a

b a b

b a c

b b a

b b b

b b c

b c a

b c b

b c c

c a a

c a b

c a c

c b a

c b b

c b c

c c a

c c b

c c c

<tbody>
</tbody>

 

[sandy666]

faster will be manually:

abc

aaa

bbb

ccc

abb

acc

baa

bcc

caa

cbb

 

[sandy666]

if there is no problem with PowerQuery here is an example excel file

Column1	Column1	Column1		Sorted
a	a	a		a, a, a
b	b	b		a, a, b
c	c	c		a, a, c
				a, b, b
				a, b, c
				a, c, c
				b, b, b
				b, b, c
				b, c, c
				c, c, c

 

[cbprado]

Great aproach, thank you!

if there is no problem with PowerQuery here is an example excel file

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Sorted[/COLOR]
a	a	a		a, a, a
b	b	b		a, a, b
c	c	c		a, a, c
				a, b, b
				a, b, c
				a, c, c
				b, b, b
				b, b, c
				b, c, c
				c, c, c

<tbody>
</tbody>

 

[sandy666]

You are welcome