3

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.

Improve this question

1 Answer 1

Reset to default
4

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

Improve this answer
4
  • 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, 2016 at 20:03
  • 1
    dates are numbers. You may change formatting to date to display them properly
    – Max Makhrov
    Oct 7, 2016 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, 2016 at 16:02
  • 1
    Yes, you may use it in any combination with other functions
    – Max Makhrov
    Oct 9, 2016 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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