Maximum Date/Timestamp

Lemme give this a shot

This formula will return the Lat/Lon (if placed in each column) for the query output:

=ARRAYFORMULA(ARRAYFORMULA(IFERROR(VLOOKUP($G$11:$G&$H$11:$H, {$A$2:$A&$B$2:$B, $C$2:$D}, 2, 0 ), 0)))

Run the query just on Select B,MAX(A)
then this formula in the column next to the query output. Adjust $G$11:$G&$H$11:$H and the output column number 2 accordingly. For some reason it kicks out an extra column of 0's for me

I was also seeing this

I put this formula nd adjusted the Columns and Rows, I got JUST THE LAT Values

You need to put the formula again in the next column for the Lon values, and as said, change the number 2 to a 3.


I changed C to D
Not 2 to 3

TimAI2 won this one, using Google Sheets.

For the record, here's a working solution using a csv download and a bunch of block procedures:

Capture Sample Data - Sheet1.csv (228 Bytes) Sheets_Latest_By_Name.aia (7.7 KB)
(I used my own data at
because I have no GPS data.)
P.S. Credits to TimAI2 for the getRange function

Thank you vey much
It was an awesome learning experience.

When user position reported

Final refining is yet to be done. Will do that subsequently.
Thank you once again @TIMAI2 and @ABG
Timer based refresh from Gogle Sheet. When fresh data received, map position updated

Got it all into one formula!!

=query(A2:D,"select max(A),B,C,D group by B,C,D order by max(A) desc Limit "&counta(unique($B$2:$B))&" LABEL max(A) ''")
  1. Using group by for the lat/lon columns
  2. Include a formula that counts the number of unique names in Column B to limit the return
1 Like

Tx a ton !
Truely Appreciate your passion !

I been trying this snce morning. I works sometimes, and sometimes it dosent.
I havent been able to gauge as to why this is happeneing !!

That first image just looks so wrong!

I will have another look at the formula....

If there is a time gap in the data being uploaded, then ths error comes.
Image 2 was earlier. Then there was a time gap

I am uploading test data to refine the app. But after a time gap, this error is recurring.

As of NOW !

For the moment, It is back to the previous query formula and the arrayFormulas for the vlookup. :frowning:

I will post a question on SO, as I cannot find anything similar out there.

I have done just that. To make the app function.

Good man :+1:

Now adding Geofence alarm

I have had a think.....

Try this formula (a bit long, but it appears to work)

=sort(filter(A1:D,match(A1:A,query(A2:D,"Select max(A) GROUP BY B ORDER BY max(A) desc LABEL max(A) ''" ),0),match(B1:B,query(query(A2:D,"Select B, max(A) GROUP BY B ORDER BY max(A) desc LABEL max(A) ''" ),"Select Col1"),0)),1,FALSE)
1 Like

Trying it out.

Seems Good to Go !! (As of Now)
Tx for ur efforts

Will test out exhaustively with breaks, to induce time gaps, and revert.