Forum Discussion
Solved
Another, hopefully quick, Dynamic Array question
With a 2D array I wanted to replace any blank values on a row with the previous value. I found this which sort of worked. =SCAN(0, N72:AJ74, LAMBDA(a,v, IF(v="",a,v))) However, the accumulator value...
=DROP(REDUCE("",SEQUENCE(ROWS(N72:AJ74)),LAMBDA(x,y,VSTACK(x,SCAN(0, CHOOSEROWS(N72:AJ74,y), LAMBDA(a,v, IF(v="",a,v)))))),1)
Does this formula return the intended result?
James_Buist Alternatively, you could use the EXPAND function to add a column at the end filled with 0's, then DROP the extra column afterwards. For example:
=LET(arr, B2:H5, DROP(SCAN(0, EXPAND(arr,, COLUMNS(arr)+1, 0), LAMBDA(a,v, IF(v="", a, v))),, -1))
See attached, if needed...
- interesting you say that as that was my work around but, not quite as smart as yours. i ADDED a column of 0s at the start - or course at the end works too. But I couldn't use expand as it was at the start so used Makearray to get an array of 0s in a column then Vstacked it. But yours is much cleaner as Expand is so much cleaner and lighter. Would be good it Expand could take a -1 to add at the front!!
Oliver's solution is really good as it also opened up how to handle other scenarios when one needs to go row by row (or column by column)
i created a let function like this and used a THUNK. Mainly so I could have an example of one for when I need to make another. But clumsy compared to yours
=LET(
_Arr,N72:AJ117,
_Rows,ROWS(_Arr),
_ArrS,MAKEARRAY(_Rows,1,LAMBDA(r,c,0)),
_ArrX,HSTACK(_Arr,_ArrS),
_fx1, LAMBDA(x, SCAN(0, x, LAMBDA(a,v, IF(v="",a,v)))),
_Op,DROP(_fx1(_ArrX),,-1),
_Op
)
My other post though, I think more of a challenge. This part feeds into it. Many thanks
https://techcommunity.microsoft.com/t5/excel/maybe-complex-dynamic-array-query/m-p/4221611
