Search code examples
excelexcel-lambda

REDUCE with conditional VSTACK inside LAMBDA. Is it possible?

I'm trying to build a VSTACK from a dynamic range using REDUCE.

What I'm getting:

enter image description here

What I want (note ignored XX value)

enter image description here

Essentially, the question is how to use conditional HSTACK inside LAMBDA inside REDUCE. Here I want to ignore cells with "XX" as a value, and create a HSTACK'd dynamic spill.

I know I could just use FILTER but this is just a simple example, where I often want to do more complex conditional edits inside a reduce depending on the cell value and then HSTACK the results (or ignore).

I don't understand what the #N/A values are due to.

HSTACK works without the conditional i.e this works (append "%" and HSTACK): enter image description here

Solution

  • Practicing With the REDUCE Function

    • I guess Rory said it all in your comments.
    • I like to put the inputs in the first row so the formula is 'LAMBDA-ready' (ready to be converted to a LAMBDA function in the Name Manager).
    • When using REDUCE, often you will use "" as the first argument, and in simple requirements like yours, a column (or a row) as the 2nd argument. The "" will yield a dummy first result and should be removed with DROP.
    • Inside the LAMBDA, I use rr and r for a column since I'm looping through rows (or cc and c for a row since I'm looping through columns).
    • It is important to understand that rr represents the current stacked result while r represents the value in the current row.
    • If you don't want to stack, keep the result (rr) as is (rewrite rr)— otherwise, stack rr and r as required.
    =LET(data,A1:A6,exclude,"XX",suffix,"%",
    DROP(REDUCE("",data,LAMBDA(rr,r,
        IF(r=exclude,rr,HSTACK(rr,r&suffix)))),,1))

    enter image description here

    LAMBDA-Ready

    • In this particular case, I will replace LET with LAMBDA and remove all arguments in the first row:

      =LAMBDA(data,exclude,suffix,
     DROP(REDUCE("",data,LAMBDA(rr,r,
         IF(r=exclude,rr,HSTACK(rr,r&suffix)))),,1))

    • Then I'll use this formula in the Name Manager and create a name e.g. StackColumnH to be able to use this LAMBDA function anywhere in the workbook:

      =StackColumnH(A1:A6,"XX","&")

    More Efficient Ways to Meet the Requirement (Hardly Relevant)

    MS365

    =LET(data,A1:A6,exclude,"XX",suffix,"%",
    TOROW(IFS(data<>exclude,data&suffix),2))

    Excel 2021

    =LET(data,A1:A6,exclude,"XX",suffix,"%",
    TRANSPOSE(FILTER(data&suffix,data<>exclude)))