# All possible permutation/combinations

#### Dr. Demento

##### Well-known Member
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.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

70 combinations

#### Oaktree

##### MrExcel MVP
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

Code:
``=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

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
Understood and updated. Thanks, Fluff.

#### Eric W

##### MrExcel MVP
Try this:

Cell Formulas
RangeFormula
K1:N20K1=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),""))
T1T1=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

##### Well-known Member
1680 Permutations? Does that sound right?

#### Fluff

##### MrExcel MVP, Moderator
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
ABCD
1NorthSouthEastWest
2NSEW
3
4
5NorthSouthEastWest
6NorthSouthEastW
7NorthSouthEWest
8NorthSouthEW
9NorthSEastWest
10NorthSEastW
11NorthSEWest
12NorthSEW
13NSouthEastWest
14NSouthEastW
15NSouthEWest
16NSouthEW
17NSEastWest
18NSEastW
19NSEWest
20NSEW
21
Sheet2
Cell Formulas
RangeFormula
A5:D20A5=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.

Replies
0
Views
72
Replies
12
Views
984
Replies
2
Views
126
Replies
3
Views
481
Replies
4
Views
300

1,181,947
Messages
5,932,918
Members
436,869
Latest member
ABGTH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back