How to turn this formula into an arrayformula?
In a google sheet, I have a long list of tickers in column A. In column B, I want to determine for each row, where the previous row was where this ticker was named. For instance, looking at the top of the sheet:
XSWX.0P0001703K is the first entry of the list. So there was no previous row in which is was mentioned (hence, PreviousRow = 0). The second time XSWX.0P0001703K is mentioned is on row 3. When was the previous time? On row 2. Etc.
I have a sample google sheet here: https://docs.google.com/spreadsheets/d/1QKp_W8q0M4RQPqQB-9oC2L-sHQcRJJD2vf3lXLJ1bD0/edit#gid=0
The formula I use to calculate the previous row is:
=ARRAYFORMULA(max(IF($A$1:$A1=A2,ROW($A$1:$A1))))
And then I just drag it down the whole sheet. And this works fine, but it has some disadvantages such as when you add new entries (sometimes in between rows), you always have to copy this (and other) formulas down. Therefore, I try to rewrite all formulas into arrayformulas that are executed over a range from the top cell.
However, this formula does not get executed over the whole range (range being A1:A), and it confused me how I would do this in this case. Do you know?
You may try:
=map(A2:A,lambda(Σ,if(Σ="",,ifna(xmatch(Σ,A1:offset(Σ,-1,),,-1),0))))
