Get latest value that is not empty

[eddie61]

Hi all

I'm trying to get a formula, that is similar to this google sheet formula but in a virtual column:

=ARRAYFORMULA(IF(LEN(A2:A);IFERROR(VLOOKUP(A2:A;SORT(FILTER(Objekt_Workinfo!B2:K;LEN(Objekt_Workinfo!D2:D));10;0);3;0));))

This formula gets the the latest value (based on a timestamp column) that is referenced by the key.

At the moment I have this virtual column formula:

ANY(SELECT(Spleisspunkte_Workinfo[Status],[DatumZeit]=MAX( SELECT(Spleisspunkte_Workinfo[DatumZeit],[Objekt Ref]=[_THISROW].[Objekte Key]))))

It almost works as intended, but there is a possibility, that in the "Spleisspunkte_Workinfo" table, the last referenced row has no value in the column [Status]. Is it possible to rewrite this formula, to get the latest value from column [Status], that is not empty?

Thanks in advance.
Adrian

[Suvrutt_Gurjar]

Maybe this 

ANY(SELECT(Spleisspunkte_Workinfo[Status],AND(ISNOTBLANK([Status]), [DatumZeit]=MAX( SELECT(Spleisspunkte_Workinfo[DatumZeit],[Objekt Ref]=[_THISROW].[Objekte Key])))))

Please note that your current expression is very sync inefficient as it has  a MAX() and SELECT() within another SELECT(),. The sync time could degrade exponentially as the number of rows it needs to work on increase.

You could try splitting the expression into two- one for MAXROW() and then finding the [Status] in another column.

[eddie61]

This doesn't work, unfortunately. It doesn't return a value, when the last row has no value in [Status].

[Suvrutt_Gurjar]

Then maybe this

ANY(SELECT(Spleisspunkte_Workinfo[Status], , [DatumZeit]=MAX( SELECT(Spleisspunkte_Workinfo[DatumZeit], AND(ISNOTBLANK([Status]), Objekt Ref]=[_THISROW].[Objekte Key])))))

[SkrOYC · Accepted solution]

I would need more info in order to suggest you any expression. I don't know which column is the key column or where you want to put this expression exactly.

Also echoing @Suvrutt_Gurjar's words, make sure you use this on an Initial Value or similar instead of a place where it would affect performance, like a virtual column.

Generally the "get the last row of data" is something you want to have when you enter new data, unless it's on a table just for reporting where there is just one row or something like that.

Again, without entering into too much detail, make sure to use expressions wisely

[eddie61]

Thanks for that response. This really made me think. I guess I need to build the functionality of my app a little different, as this virtual column would be needed on a table with 3000+ rows.

I already knew that tha arrayformula in Google Sheets significantly impacted my performance and I thought, if I change it to a virtual column it will get significantly better.

Thanks for your insight.

[Suvrutt_Gurjar]

Good that you are thinking on different lines for that functionality. My suggestion was also on inefficiencies of current approach.