0
votes

I have a google sheet that includes assessment information for students that is pulled from the testing software monthly. Each assessment taken/scheduled is a row and I am trying to condense the information so that it shows in 1 row per student. I create a pivot table which condenses most of the information into one row, but I am trying to only show the most recent test date for each student in the pivot table and can't seem to filter it by condition. I tried doing custom formula =Max(J3:J) but it still displays all of the dates and all tried the exact date filter with the same Max formula, but it only returned the two students with the most recent test date and excluded the students with an earlier last test date.

Here is a sample of the report and pivot table that I have created: https://docs.google.com/spreadsheets/d/1lL7RPJTneDLFdTQJA1uqGHdaIYwWIZYjGEjC9ZxkYMk/edit?usp=sharing

This ends up being a large report so I'm hoping there is an easy fix that can condense this to 1 row per student. We would be copying the info from the pivot table and entering it on a second sheet (Summarized data) as there is additional information that we would be entering regularly and the staff maintaining it are not tech savvy so hoping I can get this info sorted for them in the pivot table.

Thank you!

1
You can try the answer from this similar post at support.google.com/docs/thread/15963393/…. On the "Is between" filter by condition for the "Date", you can try adding =TODAY()-20 & =TODAY() (e.g. your preferred recent date range is 20 days ago from the current date).Irvin Jay G.

1 Answers

0
votes

try:

=QUERY(SORTN({'Data copied from a database report'!A2:U, 'Data copied from a database report'!A2:A&'Data copied from a database report'!B2:B&'Data copied from a database report'!M2:M}, 9^9, 2, 22, 0), 
 "select Col1,Col2,Col11,Col4,Col6,Col7,Col8,Col9,Col10,Col13,sum(Col17)
  where Col22 is not null 
  group by Col1,Col2,Col11,Col4,Col6,Col7,Col8,Col9,Col10,Col13 
  pivot Col15
  label Col1'Last Name',Col2'First Name',Col11'Phone #',Col4'Email',Col6'Address 1',Col7'Address 2',Col8'City',Col9'State',Col10'Zip',Col13'Date'")

enter image description here