Forum Discussion

tboulden's avatar
tboulden
Iron Contributor
Feb 13, 2021

Recursive Lambda: Unpacking LET function

RecentlyTwifoo posted the FIFO Inventory challenge, and PeterBartholomew1 ventured "shouldn't take lori_m more than a few minutes to write a recursive Python script to reduce any LET function back to...
excel
Formulas and Functions
TTodorov's avatar
TTodorov
Copper Contributor
Apr 06, 2024

rachelSeems that in my Excel (version 2308, build 16731.20600), TEXTSPLIT with ARRAYTOTEXT not working. I tried to recreate your data (with "|") and the result seems like this:

 

At general, result is correct, but not possible to split it.

rachel's avatar
rachel
Iron Contributor
Apr 06, 2024

TTodorov 

INDEX understands what "string" means.

Below should work for two columns. (I put your data in range A1:B4).

(Also, I guess your "for all" issue might have something to do with space).

=ARRAYTOTEXT(
    MAP(
        SEQUENCE(ROWS(A1:A4), 1, 1, 1),
        LAMBDA(row_index,
            LET(
                data, A1:B4,
                xs, TEXTSPLIT(INDEX(data, row_index, 1), ","),
                ys, TEXTSPLIT(INDEX(data, row_index, 2), ","),
                ARRAYTOTEXT(xs & "-" & TRANSPOSE(ys))
            )
        )
    )
)

 

 

  • TTodorov's avatar
    TTodorov
    Copper Contributor
    Apr 06, 2024

    rachelARRAYTOTEXT produce array which seems to not possible to split it to list (as I need):

     

    Seems that TEXTSPLIT as your first suggestion from an array is not possible for me.

    • rachel's avatar
      rachel
      Iron Contributor
      Apr 06, 2024
      Are you sure you use semi-colon instead of comma when you TEXTSPLIT?
      =TEXTSPLIT(data, ";")?
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Apr 13, 2024

        rachel 

        Yest another problem in which the solution is a nested array.  Surely, a perfectly normal situation for a dynamic array calculation engine.  The basic requirement is to allow the manipulation of multidimensional arrays held within a 2D grid (higher dimensions are represented by nesting).  To fail here is rather like PowerPivot throwing an error if more than one dimension table is present!  It undermines the purpose of the calculation.

         

        Forming a cartesian product corresponding to any individual cell in the list is easy, combining them is ridiculously difficult!

        "ThunkedCartesianProductλ"
= LET(
    list₁, TEXTBEFORE(combinedLists, "|"),
    list₂, TEXTAFTER(combinedLists, "|"),
    arr₁,  TEXTSPLIT(list₁, , ","),
    arr₂,  TEXTSPLIT(list₂, ","),
    THUNK(TOCOL(TRIM(arr₁) & "-" & TRIM(arr₂)))
 )

        The formula

        = MAP(inputList, ThunkedCartesianProductλ)

        gives the result, but as an array of 3 thunks.  Sure I can expand any array of thunks by recursive bisection, I could even do it by recursively combining blocks of 16.  The point is that to do so is a pain and I shouldn't have to do it!

         

        Since there were only 3 data cells one could obtain the result explicitly using

        = LET(
    listsϑ, MAP(inputList, ThunkedCartesianProductλ),
    VSTACK(
      INDEX(listsϑ,1,1)(),
      INDEX(listsϑ,2,1)(),
      INDEX(listsϑ,3,1)()
    )
  )

        but it wouldn't look so good for an array of a thousand thunks!

Resources