Google-sheets – Select last entry from spreedsheet

formulasgoogle sheetsgoogle-sheets-query

So I have a Google Form dumping data into spreadsheet tab form responses 1.

I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot and be sorted by the main column. My progress thus far is

=QUERY('Form Responses 1'!A2:J,
 "SELECT max(A), B, C, D, E, F, G, H, I, J group by B, C, D, E, F, G, H, I, J")

Unfortunately, this doesn't show only the latest time stamp and for some reason, the query has a weird effect of showing max in the formula cell with a spare row.

Any help would be appreciated.

Best Answer

=QUERY(A2:C5; "limit 1 offset "&(COUNT(A2:C5)-1))

Short answer

Try

=QUERY(
   LogisticsReports,
   "SELECT A,B,J WHERE A < date '"&TEXT(TODAY()-3,"yyyy-mm-dd")&"'",
   1
)

Explanation

QUERY use Google Visualization API Query Language. It handles dates differently than Google Sheets, so the result of functions that returns a date should be convert to a string having yyyy-mm-dd date format and have to be preceded by the date keyword.

Google Sheets – Using Alpha-Numeric Time Representation in a Formula

Suppose cell A2 has "7d 20h 44m" and cell B2 has "7d 18h 12m" . Then the following will compute the duration of the difference; be sure to format the output as duration for the displayed value to make sense. It will be 2:32:00.

=regexextract(A2, "(\d+)d") - regexextract(B2, "(\d+)d") + time(regexextract(A2, "(\d+)h"), regexextract(A2, "(\d+)m"), 0) - time(regexextract(B2, "(\d+)h"), regexextract(B2, "(\d+)m"), 0)

The first part, regexextract(A2, "(\d+)d") - regexextract(B2, "(\d+)d"), extracts the numbers of days and subtracts them. The second part handles hours and minutes by converting them to time like time(regexextract(A2, "(\d+)h"), regexextract(A2, "(\d+)m"), 0). The format is time(hours, minutes, seconds).

Best Answer

Related Solutions

Google Sheets Query by Max Date

Short answer

Explanation

Google Sheets – Using Alpha-Numeric Time Representation in a Formula

Related Topic