Recursive Lambda: Unpacking LET function

[tboulden]

Recently@Twifoo posted the FIFO Inventory challenge, and @Peter Bartholomew ventured "shouldn't take @lori_m more than a few minutes to write a recursive Python script to reduce any LET function back to its simplest form". I've attempted this using a recursive LAMBDA function and it is just naive replacement of a variable with its definition, please see attached. Summary on first tab, and deep-dive on 2nd tab.

I used a few of the various LET formulas from the FIFO Inventory challenge that were submitted as my set of test cases, and was able to finally get a mostly-working version; feel free to play with it, share improvements, and possibly help me figure out my remaining issues. In experimenting, my interim versions of my LAMBDA did kill my Excel instance a few times, but I think this version is stable-ish without having hit it with a bevy of test cases.

Of the 5 LET formulas I tested, only @mathetes formula will not yield to my function. Someone will probably identify the issue readily, I may just have been staring at it for too long to see it. @Sergei Baklan's formula was the only one that didn't require adjustments to var names to prevent substring collisions. Perhaps others have ideas about how to avoid having to adjust??

Finally, I've included a failed attempt on the 3rd tab that I'd like others to perform an autopsy on and perhaps help me figure out why it doesn't work, but my slightly modified version is successful. During my experimenting, I thought perhaps it was due to limits on parameter length being passed to a LAMBDA, but my brain is a bit fried after working on it for a day or two. Fun exercise!!

[#2]

@tboulden 

Wow, that is a stunning effort!  Perhaps the Microsoft Research Team might like to take a look at this to see where their 'baby' is being taken!

LAMBDA: The ultimate Excel worksheet function - Microsoft Research

I wasn't really serious in suggesting that the task should be performed, simply making the point that it could be performed.  I was simply expressing the idea that reducing a LET function to traditional spreadsheet formulae (that I know and loath) is a purely mechanistic process.  I had thought of using Lambda functions and even taken a step or two in that direction.  After all, if Lambda functions make Excel Turing-complete and, given that functional programming is well-suited to the task of parsing data for compiler writing, I thought it should be possible. 

The thoughts that led me to suggest Python were that I knew that Lori uses Python more than Excel and the development environment should be more productive.  I did have one advantage over you, namely I use Charles Williams's FastExcel which includes regular expression support, so I was able to search for strings that were bounded by word breaks and only replace those with no adjacent word characters.

I completely freaked out at the possibility of matching paired parentheses, so I guess I wasn't sufficiently committed to the task.  After all, I am the one that has declared I would sooner consign Excel to the trash-can than go back to spreadsheeting as it used to be!  To me the non-LET versions of the formulae are a mess and to be used only as a last resort.

To finish, I can only reiterate my opening sentence; Wow ...

[#3]

@tboulden 

Interesting, thanks for sharing. Can't say I understood this, make bookmark to play with your file later.

[#4]

@Peter BartholomewMuch appreciated! I did make several false starts with regard to matching opening and closing chars, but I think the saving grace is that I assumed it only should work on formula that Excel has already properly parsed and calculated; I think building a formula validator might be an interesting exercise as well.

I'll have to check out FastExcel, I've used regex in VBA with the appropriate reference, but having it available in the worksheet itself would be nice. If I recall correctly building regex for bracket matching is a bit complicated with a novice's understanding; I know that my balanced tests would fail if a brace was within a pair of double-quotes without a corresponding matching brace before the next formula-level brace.

I agree that non-LET will be a thing of the past after some time, just wish we'd had it sooner, would've made spreadsheet modeling much less aggravating at times.

[#5]

And I've sorted the issue with mathetes formula not yielding to my function: my helper LAMBDA CheckCommaBeforeOpenChar throws an error because there are no curly braces in the formula. Updated the helper LAMBDA to this, though probably better to handle errors differently than pass a value that isn't correct:

T_KthCharInstance,LAMBDA(myStr,myChar,k,IFERROR(FIND(CHAR(1),SUBSTITUTE(myStr,myChar,CHAR(1),k)),LEN(myStr)+1))

[#6]

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?

[#7]

@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
        ),
        ","
    )
)

[#8]

@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.

[#9]

@TTodorov 

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

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

[#10]

@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.

[#11]

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

[#12]

@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.

[#13]

Are you sure you use semi-colon instead of comma when you TEXTSPLIT?
=TEXTSPLIT(data, ";")?

[#14]

@rachel 
Probably was my mistake, excuse me!

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

This works perfect, many thanks! :)

[#15]

Good choice! I also like INDEX better. I get scared when excel warned me that the usage of @ is not supported in older version.

[#16]

Works perfect in this way! :)
Thanks again for working formula! :)

[#17]

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

)

[#18]

@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!

[#19]

@Peter Bartholomew 

The expected results are actually in two columns, (not joined by "-").

Like this:

So for CartesianProduct, better use @Sergei Baklan 's version, it removes TEXTJOIN TEXTBEFORE etc completely.

Also, what is "THUNK"?

=HSTACK(
    TOCOL(IF(SEQUENCE(, ROWS(list2)), list1)),
    TOCOL(TRANSPOSE(IF(SEQUENCE(, ROWS(list1)), list2)))
)

[#20]

@TTodorov Looks like you have lots of options rolling in. Just for kicks, here's a few more variants...

(1) TEXTSPLIT / TEXTJOIN with MAP (simplified - limited to 32,767 total characters):

=TEXTSPLIT(TEXTJOIN("|";; MAP(A1:A30; B1:B30; LAMBDA(a;b; TEXTJOIN("|";; TEXTSPLIT(a;; ", ") & "-" & TEXTSPLIT(b; ", ")))));; "|")

(2) MAP with TOCOL / TEXTBEFORE / TEXTAFTER:

=LET(
    arr; MAP(A1:A30; B1:B30; LAMBDA(a;b; TEXTJOIN("|"; FALSE; ""; TEXTSPLIT(a;; ", ") & "-" & TEXTSPLIT(b; ", "); "")));
    TOCOL(TEXTBEFORE(TEXTAFTER(arr; "|"; SEQUENCE(; MAX(LEN(arr) - LEN(SUBSTITUTE(arr; "|"; ))) - 1)); "|"); 2)
)

(3) Array Manipulation (LAMBDA-Free Method):

=LET(
    table; A1:B30; delim; ", ";
    a; delim & CHOOSECOLS(table; 1) & delim;
    b; delim & CHOOSECOLS(table; 2) & delim;
    count1; (LEN(a) - LEN(SUBSTITUTE(a; delim; ))) / LEN(delim) - 1;
    cols1; SEQUENCE(; MAX(count1));
    items1; TOCOL(TEXTBEFORE(TEXTAFTER(a; delim; cols1); delim); 2);
    items2; TOCOL(IFS(count1 >= cols1; b); 2);
    cols2; SEQUENCE(; MAX(LEN(b) - LEN(SUBSTITUTE(b; delim; ))) / LEN(delim) - 1);
    TOCOL(items1 & "-" & TEXTBEFORE(TEXTAFTER(items2; delim; cols2); delim); 2)
)

See attached...