google-sheets-formula

Output all unique combos for a list of items


For example, given an input array of [A,B,C,D] the output should be: [A,B,C,D,AB,AC,AD,BC,BD,CD,ABC,ABD,ACD,BCD,ABCD]

Most the answers I’ve found deal with combing multiple lists together or include ouputs such as ‘AA’ or both ‘AB’ & ‘BA’ - which I do not want.

I’d also like the formula to scale up or down dynamically based on the size of the input array and filter out blanks so I can simply point it at, say “A1:A” then as I add or remove values to the A column my list of unique combos updates.

For example, if the input array was [A,B,C] the output would be [A,B,C,AB,AC,BC,ABC].


Solution

  • Here's one approach but it can accommodate a max 18 count of the input array & post that its reaching the lambda calculation limit :

    =let(Σ,unique(tocol(A:A,1)), Δ,tocol(byrow(index(int(mod(sequence(2^rows(Σ))/2^sequence(1,rows(Σ),0),2))),lambda(Λ,join(,filter(torow(Σ),Λ)))),3),
     sort(Δ,len(Δ),1,1,1))
    

    enter image description here

    backend_matrix logic:

    enter image description here