- Excel Version
- 365
In order to generate all the permutations for three variables, put the variables in an Excel Table (which is called Inventory here). You can add as many rows to the Table as desired and you can fill in all or just one of the rows for each variable. The exemplar here will spill down 60 rows, the number of permutations for this 4_3_5 = 60 setup.
These formulas rely on Excel's new dynamic calculation engine and powerful array functions (like SEQUENCE).
If you can figure out how to set this up for more than three variables, go ahead and share it with us!
These formulas rely on Excel's new dynamic calculation engine and powerful array functions (like SEQUENCE).
If you can figure out how to set this up for more than three variables, go ahead and share it with us!
| Book1 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| H | I | J | K | L | M | N | O | P | Q | |||
| 5 | Case-Lot-Shelf | Case | Lot | Shelf | ||||||||
| 6 | Case | Lot | Shelf | A - w - 1 | A | w | 1 | |||||
| 7 | A | w | 1 | A - w - 2 | A | w | 2 | |||||
| 8 | B | x | 2 | A - w - 3 | A | w | 3 | |||||
| 9 | C | y | 3 | A - w - 4 | A | w | 4 | |||||
| 10 | D | 4 | A - w - 5 | A | w | 5 | ||||||
| 11 | 5 | A - x - 1 | A | x | 1 | |||||||
| 12 | A - x - 2 | A | x | 2 | ||||||||
| 13 | 4 | 3 | 5 | A - x - 3 | A | x | 3 | |||||
| 14 | 60 | A - x - 4 | A | x | 4 | |||||||
| 15 | A - x - 5 | A | x | 5 | ||||||||
all permut | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O5 | O5 | =Inventory[[#Headers],[Case]] |
| P5 | P5 | =Inventory[[#Headers],[Lot]] |
| Q5 | Q5 | =Inventory[[#Headers],[Shelf]] |
| O6 | O6 | =INDEX(Inventory[Case],INT((SEQUENCE(J14)-1)/((COUNTA(Inventory[Lot])*(COUNTA(Inventory[Shelf])))))+1) |
| P6 | P6 | =INDEX(Inventory[Lot],MOD(INT((SEQUENCE(J14)-1)/COUNTA(Inventory[Shelf])),COUNTA(Inventory[Lot]))+1) |
| Q6 | Q6 | =INDEX(Inventory[Shelf],MOD((SEQUENCE(J14)-1),COUNTA(Inventory[Shelf]))+1) |
| M5 | M5 | =O5&"-"&P5&"-"&Q5 |
| M6 | M6 | =INDEX(Inventory[Case],INT((SEQUENCE(J14)-1)/((COUNTA(Inventory[Lot])*(COUNTA(Inventory[Shelf])))))+1)&" - "&INDEX(Inventory[Lot],MOD(INT((SEQUENCE(J14)-1)/COUNTA(Inventory[Shelf])),COUNTA(Inventory[Lot]))+1)&" - "&INDEX(Inventory[Shelf],MOD((SEQUENCE(J14)-1),COUNTA(Inventory[Shelf]))+1) |
| H13 | H13 | =SUBTOTAL(103,[Case]) |
| I13 | I13 | =SUBTOTAL(103,[Lot]) |
| J13 | J13 | =SUBTOTAL(103,[Shelf]) |
| J14 | J14 | =Inventory[[#Totals],[Case]]*Inventory[[#Totals],[Lot]]*Inventory[[#Totals],[Shelf]] |
