Google Spreadsheet Advanced Query Solution

Question:

( by Joseph Barry )

Hello,

 I am a novice user trying to automate an job applicant evaluation spreadsheet to update when interviewers submit a form response over email. The form contains questions for who is interviewing, who is being interviewed, and what your rating is 1-10. The Form response sheet shows me the time stamp, the interviewers name, the job applicant's name, and the rating. Each interviewer may be submitting multiple entries for multiple applicants. I want Sheet 1 to update with the most recent submission by each interviewer for each job applicant. I have tried 

=filter('Form Responses 1'!$D:$D,'Form Responses 1'!$B:$B=B$1,'Form Responses 1'!$C:$C=$A2,'Form Responses 1'!$A:$A=max('Form Responses 1'!$A:$A))

but this only gives me the most recent submission from among all of the interviewers, rather than each interviewer's most recent assessment of each applicant. Can't figure out how to tie the max function for the timestamp in with the two other variables. Help please?

Here is the spreadsheet:

Solution:


Have a look at the following screenshot of 'Sheet 1' of my Spreadsheet:

I have the following formula in Cell A1:

=query(arrayformula(iferror(vlookup(query(query('Form Responses 1'!A:D;"select max(A),B,C where B<>'' group by B,C");"select Col1 offset 1";0);'Form Responses 1'!A:D;{1,2,3,4}*sign(row(A:A));false)));"select Col3,max(Col4) where Col2<>'' group by Col3 pivot Col2")

And following formula in Cell E1:

=arrayformula(if(row(A:A)=1;"Average Rating";if(A:A="";"";iferror(round((B:B+C:C+D:D)/3;2)))))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive