Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 28, 2022
Solved

LAMBDA Halloween Challenge

PeterBartholomew1 SergeiBaklan mtarler  Goal: generate an array of all possible combinations (w/repetitions) from a list of items.   For example, if there are 6 total items with 4 chosen (allowing...
excel
Formulas and Functions
office 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Oct 30, 2022

    Patrick2788 

    Finally, I have a solution!

    This time I used a Lambda function to add 1 to the previous index (stored in base n) but before returning the value, I scanned the digits and copied the larger digit across.

    WorksheetFormula
= LET(
      count, COUNTA(Items),
      initialise, REPT("0",pick),
      indices, VSTACK(
               initialise, 
               SCAN(initialise, SEQUENCE(combinations-1), NextCombinationλ(count,pick))
               ),
      INDEX(Items, 1 + Explodeλ(indices))
   )

    where

    NextCombinationλ
= LAMBDA(n,p, 
     LAMBDA(initial,s,
        LET(
            place,     n^SEQUENCE(1,p,p-1,-1),
            increment, 1 + SUM(place * Explodeλ(initial)),
            converted, BASE(increment,n,p),
            vector,    Explodeλ(converted),
            modified,  SCAN( , vector, Maxλ),
            CONCAT(modified)
        )
     )
  );

    supported by

    Explodeλ = LAMBDA(term, MID(term, SEQUENCE(1,pick), 1));

Maxλ = LAMBDA(u, v, Max(u,v));

    I have just performed the calculation for 8 picks taken for 9 items in 300ms.  Ther are 12,870 combinations and the permutations would have been over 43M!

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 01, 2022

Patrick2788 

 

I 'cleaned up' my original solution to remove SEARCH:

 

=WRAPROWS(TEXTSPLIT(TEXTJOIN("|",,DROP(SORT(UNIQUE(MAP(Arr,LAMBDA(e,IF(OR(MIN(Burst(e))=0,MAX(Burst(e))>COUNTA(Items)),"",TEXTJOIN("|",,SORT(Burst(e),,,1))))))),1)),,"|"),pick)*1

 Burst (I like 'Explode' better but I feel I'd be stealing Peter's name) being:

=LAMBDA(scalar,1*MID(scalar,SEQUENCE(,LEN(scalar)),1))

 

It caps out at 9 items, pick 5.  It doesn't matter what I use on the final matrix to convert numbers to names: MAP/XLOOKUP, MAP/CHOOSECOLS, SCAN/CHOOSECOLS, etc. Won't go beyond 9/5 because it has to look up each element.

 

As aside to this project.  Wouldn't it be nice if I could do this ('Explode' a scalar):

With this:

=TEXTSPLIT(D1,"")

The above produces a #VALUE! error.  Being able to explode a scalar with TEXTSPLIT would be really convenient.

 

 

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 01, 2022

Patrick2788 

By all means use Explode!  I think I adopted from a video demonstration by Jack Williams of Microsoft Research, so I have no claim to the name!

 

I agree that the 'null separator' should be interpreted as 'split at every character boundary'.  Not that it would do much good at present due to the fact that 

= TEXTSPLIT(list,",")

only returns the first term of each member of the list.  The justification is that Excel has never handled nested arrays so, for the sake of backward compatibility, it should do the same now!  I have never subscribed to the view that it is a good idea to compromise one's future to maintain backward compatibility to a steaming pile of junk!  In any case

= MID(list,{1,3,5,7},1)

always has worked.  Although

= TEXTSPLIT(
      TEXTJOIN(";",,list),
      ",",";"
  )

works, one runs into parameter character size problems, as I believe you have demonstrated.

 

Resources