Search code examples
arraysexcelexcel-formulapermutation

How do I get a list of permutations in one cell?

Below is 5 columns - the 6th column contains the desired outcome: the names in the 5 columns in every permutation possible.

Two rows only have two columns with values - so only two permutations in column 6 (delimited by ";").

One row has 4 values, so 24 permutations.

enter image description here

Sorry for the image, I couldn't work out how to paste a table from Excel into Stack without having to rewrite it all.

Rows in the real dataset could have 1 value, 5 values, or anything in between.

The answers here only seem to work for a two dimensional array - this array is one dimensional. I can't think of any obvious way to make it 2d so that those solutions work, nor does that seem like an efficient way to do it. One of the formulas from the above answer is below (I had a look at all of them and couldn't figure how to adapt them to my needs):

=LET(A,A1:C3,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))

Most of the solutions I've googled also don't seem to work (or I can't make them work) on a simple array of 1 row and 5 columns.

I tried to do it from scratch, and got as far as generating a list of numbers containing only the digits 1-5 and with no repititions -

=LET(firstperm,VALUE(CONCAT(SEQUENCE(1,COLUMNS(Tablestu[@[First Name]:[Preferred Last Name]])))),lastperm,VALUE(CONCAT(SORT(SEQUENCE(1,COLUMNS(Tablestu[@[First Name]:[Preferred Last Name]])),,-1,TRUE))),diff,(lastperm-firstperm)+1,list,SEQUENCE(diff,1,firstperm),wanted,(IF((ISNUMBER(SEARCH("1",list))*ISNUMBER(SEARCH("2",list))*ISNUMBER(SEARCH("3",list))*ISNUMBER(SEARCH("4",list))*ISNUMBER(SEARCH("5",list))),list,"")),FILTER(wanted,wanted<>"",""))

Thinking I could then somehow split up these 5 digit numbers, and use an INDEX formula to return words in that order. I.e. 31452 would return the third word, then the first, then the fourth, and so on... but I am still a million keyboard-head-smashes away from turning this into what I need it for, and it will be stupidly inefficient when I even get there.

The answer linked seems like the right kind of thing - I'm using Excel 365 and I need a solution that uses formulas, not VBA or power query.

LAMDAs and LETs are fine.

Solution

  • Alternate solution:

    =LET(range, A1:E1,
     f,     FILTER(range,range<>""),
     c,     COLUMNS(f),
     s,     c^c,
     a,     MAKEARRAY(  s, c,
               LAMBDA( rw, cl,
                       MOD(CEILING(rw/(s/(c^cl)),1)-1,c)+1)),
     p,     FILTER(a,
                   BYROW(  a,
                   LAMBDA( x,
                           AND(MMULT(N(TRANSPOSE(x)=x),SEQUENCE(c)^0)=1)))),
TEXTJOIN(";",,UNIQUE(BYROW(p,LAMBDA(x,TEXTJOIN("",1,CHOOSECOLS(f,x)))))))

    This also takes into account that if a duplicate name is used within the range, it won't generate duplicate permutations.

    EDIT: Solution based on version suggested by Bo Rydobon on Linked In (So credits to Bo, many thanks!).

    =LET(z,TOROW(A2:L2,3),TEXTJOIN(";",,REDUCE(TOCOL(z),DROP(z,,1),LAMBDA(a,w,TOCOL(IFS(ISERR(FIND(z,a)),a&z),3)))))

    This still leaves us with the character limit of the cell, but we could also spill the range:

    =LET(z,TOROW(A2:L2,3),REDUCE(TOCOL(z),DROP(z,,1),LAMBDA(a,w,TOCOL(IFS(ISERR(FIND(z,a)),a&z),3))))

    This alows up to 9 different cells containing a string to be permutated, after that we meet the boundaries of the excel sheet rows and it'll throw an error.