

August 6, 2020

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


Trusted Members
Moderators
Power BI

January 31, 2022

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.
1 Guest(s)
