Hello all,
I got stuck at a recursive Lambda Function.
First, I have two columns. Column A contains a list of names like "Peter", "John", "Mike". Column B contains a list of numbers 3, 2, 5.
At the very end, as a result I need to get an dynamic array of "Peter", "Peter", "Peter", "John", "John", "Mike", "Mike", "Mike", "Mike", "Mike".
For each row I got a formula like TEXT(SEQUENCE(B1;;0;0);"")&A1
This formula returns three times the name Peter, and so on.
But how can I wrap this function into a Lambda Function so I can select the values from column A and column B to get the final array as shown above.
I started with something like LAMBDA(name, no_repetition, IF(name = "" , "" , LISTREP(name, no_repetition, TEXT(SEQUENCE(no_repetition;;0;0);"")&name)))
The formula LISTREP would be the named lambda function I try to call inside the function itself in order to make it recursive.
Does anyone have an idea?!
Thanks in advance!
All best
Peter
Couldn't think of a LAMBDA solution but came up with a not very pretty, but working, formula that uses many of the dynamic array functions that Excel offers nowadays.
=TEXTSPLIT(TEXTJOIN("",,REPT(TAKE(myData,ROWS(myData),1)&",",TAKE(myData,ROWS(myData),-1))),,",",TRUE)
where "myData" refers to the range A1:B3 with names in A and numbers in B.
See attached.
And then, since I'm a big fan of Power Query, I also added a very simple solution using that tool. See if either of these work for you.
