2

I'm working on a spreadsheet that records logistics reports via a Google form. We have 100 locations and logistics personal visit and report on condition of each location, i.e. the location needs maintenance etc.

We have 3 categories of location: Priority locations that need to be visited daily, secondary locations that need to be visited a minimum every other day, and other locations that we aim to visit a minimum of every 3 days.

Each logistics visit is recorded in the spreadsheet via the form it captures:

Column A = Time Stamp,  
Column B = Location,  
Column C = Damage Yes/No,  
Column D = Damage Description,  
Column E = Personnel ID,  

I want to create a query that groups the entries by location so that only the last visit for each location is displayed.

And use conditional formatting relating to latest visit date to highlight:
Green visited today,
Amber visited yesterday,
Red visited three days ago.

3

Query is not an option for your task, try this formula:

=ArrayFormula(VLOOKUP(UNIQUE(FILTER(B2:B,B2:B<>"")),QUERY(SORT(A2:E,1,false),
"select Col2, Col1, Col3, Col4, Col5"),{2,1,3,4,5},0))

enter image description here

  • That looks great. @MaxMakhrov It works but the dates are displaying as numbers rather than time stamps. Perhaps it's important to note that the time stamp is Date and time DD/MM/YYY HH:MM:SS. If I don't want to do it on the main sheet, I'd rather import the data into another tab and do it there, to protect the original data. Can I use importrange or a named range. Is it possible to insert group by into this query? =QUERY( LogisticsReports, "SELECT A,B,J WHERE A > date '"&TEXT(TODAY()-7,"yyyy-mm-dd")&"'", 1 ) – Malcolm Lesley Oct 6 '16 at 20:03
  • 1
    dates are numbers. You may change formatting to date to display them properly – Max Makhrov Oct 7 '16 at 5:59
  • Is there a way to put this =ArrayFormula in a separate tab using importrange or a Named Range? – Malcolm Lesley Oct 8 '16 at 16:02
  • 1
    Yes, you may use it in any combination with other functions – Max Makhrov Oct 9 '16 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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