Excel LAMBDA - abstracting a common REDUCE/VSTACK pattern

[@conquizapateria7134]

Just Great application of reduce

[@DimEarly]

That STACKER function is great! So simple, and so many uses…

[@flexyourdata]

Thanks, @DimEarly. Found myself doing TAKE/REDUCE(DROP/STACK so many times just figured it would be worth simplifying it a bit

[@AdolfoLeonSepulveda]

Thanks, it's amazing!

[@Excelambda]

Great video!! Algorithm showed me this video now and I got in because your name rings a bell. Maybe from a forum but I do not know which one.
Anyhow, liked, subscribed ✌🏼😉
And for a solution, there is one very simple, any lambda helper functions free:
=LAMBDA(n, t, k,
LET(
c, SEQUENCE(, MAX(t)),
i, t >= c,
HSTACK(INDEX(n, TOCOL(IFS(i, SEQUENCE(ROWS(t))), 2)), TOCOL(IFS(i, c) + TEXTBEFORE(k, "-") - 1, 2))
)
)
n, names ; t, tickets nr. ; k, range

[@JoseAntonioMorato]

I learned a lot from your video, but I found your solution a bit complicated.
I developed a LAMBDA not for just one line, but for the entire data set:
=LAMBDA(DataTable,
LET(NM,INDEX(DataTable,,1), NT,INDEX(DataTable,,2), RN,INDEX(DataTable,,3),
Names,TOCOL(IF(SEQUENCE(1,MAX(NT))

[@flexyourdata]

Great solution! The technique in the video was trying to create something reusable for other problems of the same type. There is also a follow-up video which extends on this. Thanks for sharing your formula.

[@Fxingenieria]

@JoseAntonioMorato / José, parabéns, encontrei mais uma proposta sua para um problema complexo, uma solução espetacular que você deu.

[@sunnybaggu785]

Thank you sir, for making this video, very helpful information.
Vstack with Reduce examples are not there in RUclips so far...
This video is a unique one.
One request would be, that the sound of the video is a bit low. You can consider to check the same next time.
Learning from your content is always good and new.

[@flexyourdata]

Thanks for the encouragement and feedback. You'll hopefully hear in the next video that I'm using a new mic and the sound quality is better. 👍

[@cedjulemckeever]

Another nice video. Just wondering if there was any way to add a Header Row to the final output?

[@flexyourdata]

Generally I would recommend keeping the presence of a header separate from the processing of the data.
In the example shown in the video, you could do something like this:
=LET(AllData, B8:D14, VSTACK( TAKE(AllData, 1) , STACKER( DROP(AllData, 1) , ProcessARow) )

[@cedjulemckeever]

@@flexyourdata Thanks for the reply. I was hoping for something simpler. I will take your advice and keep the header separate from the data. Keep up the good work.

[@m.bouguerra]

bonjour, j'ai appliquer tout les étapes de calcul mais j'ai une erreur #REF /
=LET(
data, $A$4:$C$9,
seq, SEQUENCE(ROWS(data)),
firstRow, ProcessArow(TAKE(data, 1)),
reducer, REDUCE(
firstRow,DROP(seq, 1),
LAMBDA(acc,curr,
LET(
thisRow, INDEX(data, curr),
VSTACK(acc, ProcessArow(thisRow))
)
)
),
reducer
)
TU peux me corriger

[@denisroberts]

I think I had the same error as @m.bouguerra - I was getting a #REF! error, probably being caused by the INDEX function. As a workaround, I replaced INDEX(data, curr) with TAKE(TAKE(data,curr),-1)

[@denisroberts]

CHOOSEROWS(data,curr) also works - and looks better!

[@m.bouguerra]

Thanks for this@@denisroberts

[@flexyourdata]

Thanks, that makes sense. INDEX without a column arg may be dependent on the array being a certain shape. TAKE or CHOOSE* are the more reliable options.b

[@gabpugliese]

In the definition of thisRow make explicit the placeholder for columns. INDEX(data,curr,) instead of INDEX(data,curr) . The error #REF dissapear