Forum Discussion
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 is carried forward to the next row so if there is a value at the end of the previous row, it is carried forward to the next row as SCAN just works its way through the array. I want to treat each row independently so that there is nothing carried forward to the next row.
I tried with ByRow but this caused a calc error. Couldn't find a way to 'get' the row number or to determine the row had changed and thus be able to set a to 0. Or, to get ByRow to work which would have been the easiest.
This could apply to the MAP function too as getting the row and column index could help determine a new column or row
=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?
=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?
- It absolutely does. Wow. Brilliant. That concept will help elsewhere as well. Many thanks
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
