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
rachel's avatar
rachel
Steel Contributor
Apr 06, 2024

TTodorov 

 

I need to use @ to convert array into a string to use textsplit in let, I not sure whether it is supported on your excel, but it works for me:

 

=TOCOL(
    TEXTSPLIT(
        ARRAYTOTEXT(
            MAP(
                A2:A4,
                LAMBDA(x,
                    LET(
                        data, TEXTSPLIT(x, "|"),
                        xs, TEXTSPLIT(@TAKE(data, 1, 1), ","),
                        ys, TEXTSPLIT(@TAKE(data, 1, -1), ","),
                        ARRAYTOTEXT(TOROW(xs & "-" & TRANSPOSE(ys)), 0)
                    )
                )
            ),
            0
        ),
        ","
    )
)

 

TTodorov's avatar
TTodorov
Copper Contributor
Apr 06, 2024

rachel 
In fact there is no need to split with |.
My table have 2 columns:

In sample table, when I meet "For all" in second column, need to replace it with another list.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 13, 2024

    TTodorov 

    As variant for the sample table

    =LET(
  splitter, LAMBDA(pair,
    LET(
        a, TEXTSPLIT(INDEX(pair,1,1),,", "),
        b, TEXTSPLIT(INDEX(pair,1,2),, ", "),
        LAMBDA(HSTACK(
            TOCOL( IF(SEQUENCE(,ROWS(b) ),  a ) ),
            TOCOL( TRANSPOSE( IF(SEQUENCE(,ROWS(a) ),  b ) ) )
        ) )
    )
),
unpacker,
LAMBDA(arrayOfThunks,
    LET(
        n, ROWS(arrayOfThunks),
        first, INDEX(arrayOfThunks, 1, 1)(),
        rest, INDEX(arrayOfThunks, SEQUENCE(n - 1, , 2), 1),
        IF(
            n = 1,
            first,
            REDUCE(first, rest, LAMBDA(a,v, VSTACK(a, v())))
        )
    )
),

one, LAMBDA(v, INDEX(v,1,1)),
two, LAMBDA(v, INDEX(v,1,2)),
getAll, LAMBDA(v,
    TEXTJOIN(", ",,
        FILTER(
            ObjetsPositions4[Object],
            ObjetsPositions4[Position] = VALUE(v)
        )
    )
),
rawSplit, unpacker( BYROW(SampleTable5, splitter )),
sortPositions, SORT(ObjetsPositions4, {1,2} ),
setNine, LAMBDA(pair,
    LAMBDA(
        HSTACK(
            IF(one(pair)="99",
                INDEX(
                CHOOSECOLS(sortPositions,2),
                XMATCH( VALUE(two(pair)),
                        CHOOSECOLS(sortPositions,1),
                        0,
                        -1
                )
                ),
                one(pair)
            ),
            two(pair)
        )
    )
    ),
getNine, unpacker( BYROW(rawSplit, setNine )),
setAll, LAMBDA(pair,
    LAMBDA(
        HSTACK( one(pair),
            IF(two(pair)="For all",
                getAll(one(pair)),
                two(pair)
            )
        )
    )
    ),

withAll, unpacker( BYROW(getNine, setAll ) ),
VSTACK(
    {"Position","Object"},
    unpacker( BYROW(withAll, splitter ) )
)

)
  • rachel's avatar
    rachel
    Steel Contributor
    Apr 06, 2024

    TTodorov 

    Oh, I see "|" is the mark down. 

     

    What is supposed to happen when there is "For all"? 

     

    • 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
        Steel 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))
            )
        )
    )
)

         

         

Resources