All possible permutation/combinations

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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.
 
Upvote 0
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’)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top