Google Sheets Summary Data - select newest row based on date

Question

I have what is essentially a flat-file database receiving form entry data into a Google Spreadsheet collecting group positions changes every year or as they occur. So the date is the Key for this project. In a different workbook I am trying to create a summary report by Group and Position.

All is fine with the code I have if I want ALL positions ever, but I only want the latest entry per position, even if the position hasn't changed in 4 years, or perhaps just today. I have attempted various vlookup, sortn, and unique techniques, learning plenty but not succeeding in my goal.

For the flat file ColA is Group number (text) ColB is position, (we can just call it position1, position2, etc, normalized to be standard [form entered]) ColC thru T is all personal data, ColU is the entry date. So for example, Group 101 - Position1 might have 4 rows with different dates (and perhaps people) and I need the latest one. Thus the person in the position right now. So the report would look like:

Group 101 ~ {group name/description}

Group 101 Position1 ~ personal info

Group 101 Position2 ~ personal info

Group 101 Position3 ~ personal info

Group 102 ~ {group name/description} and so on.

My code:

=query(importrange("[WORKBOOK KEY]","Personal Information!A2:Y9000"),"select Col1,Col2,Col3,Col4,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 where Col1 contains '"&$A$6&"')

Where A6 would be 101, and I'd several lines down I'd have the same code referencing perhaps A10 etc.

(I go to Col Y in the case I end up expanding the data set for some reason.)

Also, depending on results, I am not opposed to coding each position per group in the summary, where the date function may put them out of order per group. They are not numerically nor alphabetically sortable, AND organizational structure is not necessarily in stone position wise. I have been successful adding a second Query criteria to that code to pick up Position for line by line search, but completely unsuccessful and lost in getting the date comparison to function.

For date format I have 2 available. 3/10/2021 (ColU) form data sourced via Ajax date picker, and 2021-04-17 19:48:21 source via form entry time stamp. I'd prefer to work with ColU.

All assistance is highly appreciated!!

Edit 5/3/2021

Thank you very much for timely replies. Many irons in the fire so I haven't been as timely.

To include some editable models, I have copied both a data source workbook, and the query/report workbook for a working model with subsets of the data. You will notice group 101 has several entries, this models updates over time. In this case months rather than years. I would need to capture the newest entry for each position/group combination. What I have will work by changing the date in cell A2 (Directory workbook) with the assumption that ALL groups update every year.

That requires a management step to change that date, and leaves a pile of empty sub-reports as groups report. There is some utility to that, but I'd rather use some conditional formatting (cell colors for old data) for that function. Also notice, in the current form of the report, it returns everything after that date, which would fail if formatted with only the number of rows available for the positions, where the query would not over-write the next query returns. I moved the next two groups lower for that illustration. There would only be 5 rows between groups.

Also, I do the reporting in a separate workbook to prevent, as much as possible, data corruption in the original/data sheet. (I learned by a very time expensive experience.)

Data sheet: https://docs.google.com/spreadsheets/d/10JSg9vjFTFZ9ihjENyrY4Qgch7HzmWiWahHUKpvAvWY/edit?usp=sharing

Report sheet. https://docs.google.com/spreadsheets/d/1qP3QB4WLHuxUYGnN13L3GG2Lffvs1q0RXL2ZP7_BQcE/edit?usp=sharing

Many thanks in advance again!!

Welcome. Please read how to and share a test sheet so as you can be easier helped. — marikamitsos

doubleunary doubleunary · Accepted Answer · 2021-04-18T09:35:04

The easiest way to show the latest entry report in the State Directory spreadsheet is to import the raw data to helper sheets with importrange() formulas like this:

=importrange("10JSg9vjFTFZ9ihjENyrY4Qgch7HzmWiWahHUKpvAvWY", "Council Personal Information!A1:Z")

To get a shorter table with just the latest entry for each position, choose Insert > New sheet, and put these formulas in cells A1 and A2 of the new sheet:

={ 'Council Personal Information'!A1:Z1 }

=arrayformula( 
  iferror( 
    vlookup( 
      unique('Personal Information'!A2:A & 'Personal Information'!B2:B), 
      sort( 
        { 
          'Personal Information'!A2:A & 'Personal Information'!B2:B, 
          'Personal Information'!A2:Y 
        }, 
        'Personal Information'!U2:U, 
        false 
      ), 
      column('Personal Information'!A2:Y) + 1, 
      false 
    ) 
  ) 
)

I have added these formulas in the State Directory spreadsheet. You can hide the importrange() sheets if they get in the way.

Google Sheets Summary Data - select newest row based on date

1 Answers