All possible permutation/combinations

[Dr. Demento]

Good morning!

Forgive me, but I don't remember the difference between permutations and combinations, so hopefully my explanation points in the proper direction.

I have two ordered lists (NORTH|SOUTH|EAST|WEST) and (N|S|E|W) and I want to make a listing of all permutations/combinations?? substituting N for NORTH, S for SOUTH, etc. The order of the values is fixed as shown.

For example, if I start with the first row, I want all possible perms/combos with both the full word and the abbreviation, as shown with the next four lines.

|-------|-------|-------|-------|
| NORTH | SOUTH | EAST | WEST |
|-------|-------|-------|-------|
| N | SOUTH | EAST | WEST |
|-------|-------|-------|-------|
| N | S | EAST | WEST |
|-------|-------|-------|-------|
| N | S | E | WEST |
|-------|-------|-------|-------|
| N | S | E | W |
|-------|-------|-------|-------|

This is just a partial example; I was trying to manually jam this out and kept getting screwed up.

Any help would be much appreciated.

Thanks y'all.

 

[johnnyL]

70 combinations

 

[Oaktree]

If you're using Office 365 with access to the new LET, LAMBDA, MAKEARRAY, and UNIQUE functions, you can use something like this: Generate All Permutations in Excel using LAMBDA

Set B2:E2 = North, South, East, and West
Set B3:E3 = N, S, E, and W

Then, use

=LET(A,B2:E3,B,ROWS(A),C,COLUMNS(A),D,B^C,E,UNIQUE(MAKEARRAY(D,C,LAMBDA(rw,cl,INDEX(IF(A="","",A),MOD(CEILING(rw/(D/(B^cl)),1)-1,B)+1,cl)))),FILTER(E,MMULT(--(E<>""),SEQUENCE(C,,,0))=C))

to output the dynamic array

 

[Dr. Demento]

Oaktree, that looks like magic, but alas, I'm on Office 2016/2019. Thanks for the reference!!

Any thoughts for a VBA solution? Sorry; should have specified that.

 

[Fluff]

I'm on Office 2016/2019

In that case I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dr. Demento]

Understood and updated. Thanks, Fluff.

 

[Eric W]

Try this:

Book1
	A	B	C	D	E					J	K	L	M	N	O					T
1	NORTH	SOUTH	EAST	WEST							NORTH	SOUTH	EAST	WEST						16
2	N	S	E	W							NORTH	SOUTH	EAST	W
3											NORTH	SOUTH	E	WEST
4											NORTH	SOUTH	E	W
5											NORTH	S	EAST	WEST
6											NORTH	S	EAST	W
7											NORTH	S	E	WEST
8											NORTH	S	E	W
9											N	SOUTH	EAST	WEST
10											N	SOUTH	EAST	W
11											N	SOUTH	E	WEST
12											N	SOUTH	E	W
13											N	S	EAST	WEST
14											N	S	EAST	W
15											N	S	E	WEST
16											N	S	E	W
17
18
19
20
Sheet13

Cell Formulas
Range	Formula
K1:N20	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))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[johnnyL]

1680 Permutations? Does that sound right?

 

[Fluff]

Understood and updated. Thanks, Fluff.

Thanks for that.

Just for the hell of it, another 365 formula that does not need Lambda.

+Fluff 1.xlsm
	A	B	C	D
1	North	South	East	West
2	N	S	E	W
3
4
5	North	South	East	West
6	North	South	East	W
7	North	South	E	West
8	North	South	E	W
9	North	S	East	West
10	North	S	East	W
11	North	S	E	West
12	North	S	E	W
13	N	South	East	West
14	N	South	East	W
15	N	South	E	West
16	N	South	E	W
17	N	S	East	West
18	N	S	East	W
19	N	S	E	West
20	N	S	E	W
21
Sheet2

Cell Formulas
Range	Formula
A5:D20	A5	=LET(d,A1:D2,r,ROWS(d),c,COLUMNS(d),s,SEQUENCE(,c),b,MID(BASE(SEQUENCE(r^c,,0),r,c),s,1)+1,INDEX(d,b,s))
Dynamic array formulas.

 

[johnnyL]

1680 Permutations? Does that sound right?

Yes @johnnyL that is exactly right.