How to stop REDUCE() from returning "nothing" while still acting on the first index in an array?
Howdy all,
I am trying to act on each value in an array (for the sake of this example, a column of values). REDUCE lets me iterate through them and act on each value. I want to return the outputs in another column, and am using VSTACK to build that column array. The problem is, if I put "" as REDUCE's accumulator value, it literally adds in a blank cell at the first index of the array, and if instead I leave the accumulator value blank, it will not actually act on the first value (instead, filling index 1 of the array with the first value in REDUCE's target array). To see what I am talking about, put any random words in a column (A1:A5 for this example), then use the following on it:
=REDUCE(,A1:A5,LAMBDA(a,b,VSTACK(a,b&" you")))
The goal should be "word1 you" in the first output cell, "word2 you" in the second, etc. Instead you get "word1","word2 you","word3 you","word4 you", "word5 you" in cells 1-5.
Now, if you do this:
=REDUCE("",A1:A5,LAMBDA(a,b,VSTACK(a,b&" you")))
You get the following in output cells 1-6:
"","word1 you","word2 you","word3 you","word4 you", "word5 you".
I have a workaround, where I make the accumulator value equal to index1 of the array I am acting on, then in the lambda check for "when" that value is equal to the part of the array being acted on (obviously the first time), have to wrap it in an AND() to make the array scalar, and if it's the case perform the action on the accumulator, otherwise perform it on the portion of the array. It works, but it is ugly/unwieldy makes the formula excessively long, and is inelegant. I feel there must be a way to understand the logic and make it process on each portion inside the lambda itself.
What works:
=REDUCE(INDEX(A1:A5,1),A1:A5,LAMBDA(a,b,IF(AND(a=b),a&" you",VSTACK(a,b&" you"))))
Attempting instead just to check in the LAMBDA portion IF(a=""), IF(ISNA(a), IF(ISBLANK(a), etc, any of those just throws errors. Anyone have a better understanding of REDUCE to make this prettier?
[link] [comments]
Want to read more?
Check out the full article on the original site