Google Sheets query + array + sort + transpose?

Question

I'm working on a dynamic dashboard in Google Sheets that uses response validation to choose an student's name from a drop-down, pulling the relevant information for that particular student and adjusting the graphs/charts. One of the sections of the dashboard shows a list of events and the dates they happened, in chronological order. The order of events changes based on the order the dates happened in, which are pulling from a separate sheet (called "Database"), meaning the order of the events changes for each student.

I'm trying to create a formula that will locate the row for the currently selected student in the Database sheet, create an array with the headers of the Database sheet (for the event names) and the one row that matches the selected student's name, put that array in order by chronological date, and then transposes it so that it's a list of events in one column and their date in the other column.

I've created a copy of the dashboard and removed/edited all student information. In the sheet called "Student Tracker", I'm working in cell J7 (colored purple). It needs to pull the dates for the selected student and the header row (to label the dates) from the sheet called "studentList".

Thanks in advance for any help you can provide!

Answer 1

try:

=QUERY(TRANSPOSE(QUERY(studentList!A:Q, 
 "select E,F,G,H,I,J,K,L,M,N,O,P,Q 
  where A ='"&C4&"'", 1)), 
 "select Col1,' ',Col2 
  where Col2 is not null 
  order by Col2 label ' '''", 0)

note that columns J and K are merged so one empty column needed to be included in outer QUERY