Forum Discussion

TheDub's avatar
TheDub
Iron Contributor
Jul 10, 2024
Solved

Another Issue with previous row/column references

This is a (simpler, I believe) variant of referring to PREVIOUS rows/columns issue but I still can't get my head around it: Given this: I want to insert in B2 a dynamic function which will sh...
excel
Formulas and Functions
office 365
  • djclements's avatar
    djclements
    Jul 11, 2024

    TheDub For a single row of data, the SCAN function can be used with cell A2 as the initial_value and range B1:E1 as the array:

     

    =SCAN(A2, B1:E1, LAMBDA(a,v, v-a))

     

    This can be copied down in column B as needed, to apply the function to multiple rows. However, if you want a single dynamic array formula to cascade downwards, spilling an array of arrays, it gets a bit more complicated. One possible solution is to use TEXTJOIN to store the results of each row, then TEXTSPLIT to access the data from the previous row:

     

    =LET(
    results, SCAN(TEXTJOIN("|",, B1:E1), A2:A5, LAMBDA(a,v, TEXTJOIN("|",, SCAN(v, TEXTSPLIT(a, "|"), LAMBDA(p,c, c-p))))),
    --TEXTBEFORE(TEXTAFTER("|" & results & "|", "|", SEQUENCE(, COLUMNS(B1:E1))), "|")
)

     

    The results of the double-SCAN will return a single column of delimited text strings...

     

    Delimited Results

     

    ...which are then split into the final 2D array using TEXTBEFORE and TEXTAFTER.

     

    Final Results

djclements's avatar
djclements
Silver Contributor
Jul 11, 2024

TheDub For a single row of data, the SCAN function can be used with cell A2 as the initial_value and range B1:E1 as the array:

 

=SCAN(A2, B1:E1, LAMBDA(a,v, v-a))

 

This can be copied down in column B as needed, to apply the function to multiple rows. However, if you want a single dynamic array formula to cascade downwards, spilling an array of arrays, it gets a bit more complicated. One possible solution is to use TEXTJOIN to store the results of each row, then TEXTSPLIT to access the data from the previous row:

 

=LET(
    results, SCAN(TEXTJOIN("|",, B1:E1), A2:A5, LAMBDA(a,v, TEXTJOIN("|",, SCAN(v, TEXTSPLIT(a, "|"), LAMBDA(p,c, c-p))))),
    --TEXTBEFORE(TEXTAFTER("|" & results & "|", "|", SEQUENCE(, COLUMNS(B1:E1))), "|")
)

 

The results of the double-SCAN will return a single column of delimited text strings...

 

Delimited Results

 

...which are then split into the final 2D array using TEXTBEFORE and TEXTAFTER.

 

Final Results

Resources