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 10, 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

TheDub's avatar
TheDub
Iron Contributor
Jul 11, 2024

First - to Harun24HR: thanks for the answer; seems djclements beat you by about 10 minutes (and added something I'll get back to below) so he should get the credit for best response.

 

Second - to myself: a strong and loud:facepalm: for not thinking about SCAN(); hopefully this won't happen again (but I doubt it...)

 

Third - to djclements: thanks for you answer. Yes, I was indeed thinking about the next step: using the answer (once I have it) dynically over a number of rows with different starting values; I just usually like to ask about one issue at the time in order to keep it in focus. But now that you brought that up - yes, that works too (and thanks again). I have to say, though, that I hate that Excel makes us use crutches like TEXTJOIN()/TEXTSPLIT() (and their cousin from a different grandmother - FILTERXML()....) just to avoid the dreaded array-of-array issue. It would interesting to see if @Peter Bartholomew could unleash his thunks on this (although I'm not sure I entirely - or partially - understand them either).

 

Which brings us to the next interesting variation on this: in your answer the reference row/the array being SCANned is always Row 1. What if we wanted to make that dynamic as well, so that each row scans the row immediately above it (or even, perish the thought, the Nth row about it)? Is that going to be an array-or-arrays-of-arrays problem:stareyes:?