Forum Discussion
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...
Are you sure you use semi-colon instead of comma when you TEXTSPLIT?
=TEXTSPLIT(data, ";")?
=TEXTSPLIT(data, ";")?
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! 🙂
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...
- Works perfect in this way! 🙂
Thanks again for working formula! 🙂 - Good choice! I also like INDEX better. I get scared when excel warned me that the usage of @ is not supported in older version.
