0

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

enter image description here

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.

enter image description here

Any help would be appreciated.

  • I found/figured out the answer via another thread. My solution was: =FILTER('Form Responses 1'!A2:J, MATCH( 'Form Responses 1'!A2:A , QUERY('Form Responses 1'!A2:C , "select max(A) group by lower(C)" ) , FALSE ) ) – Dustin Taylor Jan 10 at 16:41
0

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

4

  • Hey, thanks for the response. imgur.com/a/sxOJ9Ne I attached an image giving an example of what my response form would have. What I am actually needing is for it to return the latest timestamp of each unique pilot. So in my image it would need to return everything except for the Main AC Pilot BD 1/4/19 line. – Dustin Taylor Jan 10 at 14:34
0

={{A1:J1}; {IFERROR(ARRAYFORMULA(VLOOKUP(QUERY(QUERY({QUERY(QUERY(A2:J,
 "select *", 0),
 "select Col3,Col1,Col2,Col4,Col5,Col6,Col7,Col8,Col9,Col10 order by Col1 desc", 0), A2:A},
 "select Col1, count(Col11) where Col11 is not null group by Col1 label count(Col11)''", 0),
 "select Col1", 0),QUERY(QUERY(A2:J,
 "select *", 0),
 "select Col3,Col1,Col2,Col4,Col5,Col6,Col7,Col8,Col9,Col10 order by Col1 desc", 0),
 {2, 3, 1, 4, 5, 6, 7, 8, 9, 10}, 0)), )}}

4

0

={{A1:J1}; {FILTER(A2:J, MATCH(A2:A, QUERY(A2:C, "select max(A) group by lower(C)"), 0))}}

4

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.