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

Hi,
I'm trying to transform this table (two columns, separated in text bellow with symbol "|"):
3 | 138, 169, 192, 193
3, 4 | 127
2, 3 | 115, 138, 144

to this column:
3-138
3-169
3-192
3-193
3-127
4-127
2-115
2-138
2-144
3-115
3-138
3-144

I prepared with helper table, wit formula TOROW(TEXTSPLIT($A2, , ", ") & "-" & TEXTSPLIT($B2, ", ")) for each row.
In sample above $A2 = 3 and $B2 = 138, 169, 192, 193
After that with TOCOL() from helper table will have result column as mentioned.

But wonder for better way (without helper table).

Is it possible to be done with recursive LAMBDA?

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

)

Resources