All possible permutation/combinations

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
608
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

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.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,105
Office Version
  1. 365
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
Joined
Nov 2, 2010
Messages
608
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
79,873
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,562
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
Joined
Nov 7, 2011
Messages
3,992
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
1680 Permutations? Does that sound right?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,873
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
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
Top