Consolidate response data from Google Form submissions where the respondents have the same name

0
votes

I thought I could figure this out, but it seems I can't. Basically, students' information will be input to Google Sheets through Google Forms, but due to my Google Forms having sections, the data of one student appears in different rows. Each student rates 4 subjects, and they each have 4 teachers; the form separates each subject area into sections and Google Sheets places this info into 4 different rows.

I'm trying to consolidate all the data that has an identical name into one row, rather than appearing in 4 different rows. I've recreated what my data currently looks like in this example spreadsheet.

Any help would be much appreciated, this is driving me mental!

1
google-apps-scriptgoogle-sheetsgoogle-forms
What have you tried? I recommend looking into the worksheet function Query. PS: if you require the students to submit all data in a single form submission, you will not need to perform the "reassembly", as all the data will be in the same submission. - tehhowch
I've tried excel formulas and none can do what I want. I thought this would be relatively easy, but I was wrong. I read about Query, might be beyond me, but I'll give it a go. - JessBee

1 Answers

1
votes

You could do it with a lot of VLOOKUP's.

Suppose you put the list of names in A21:A24

=unique(C2:C11)

Then do a lookup on each Name/subject pair in the appropriate column

=ArrayFormula(iferror(vlookup(A21:A24&{"Math","Math","English","English","Science","Science","HSIE","HSIE"},{C2:C11&D2:D11,E2:N11},{2,3,4,5,6,7,8,9},false),""))

enter image description here

Explanation

Maybe the way to explain it is to build it up from the case of one student - one subject to many students - many subjects? This assumes that you've got a list of student's names in A21:A24 and you want to pull out the info for each of them from the original data.

(1) One student, one subject

Hopefully trivial - just provide a reference to the student's report

=E2

(2) Several students, one subject (maths) (Names not necessarily in same order as original list)

Use vlookup to get report against each name

=ArrayFormula(vlookup(a21:a24,C2:E11,3,false))

It's an array formula so that it works through each of the 4 names, finding the first report associated with each. Note that it would pick up a blank for Susan in your data, because the first row with Susan in it contains a blank for maths.

(3) Several students, several subjects

=ArrayFormula(iferror(vlookup(A21:A24&{"Math","Math","English","English","Science","Science","HSIE","HSIE"},{C2:C11&D2:D11,E2:N11},{2,3,4,5,6,7,8,9},false),""))

So here it's looking up pairs like

BobMath BobMath BobEnglish BobEnglish...JimMath JimMath JimEnglish...

(student name + first pair of curly brackets)

in an array (second pair of curly brackets) where the first column is 

BobMath
JimMath
SusanScience
...

and pulling out the correct row. It's also got to tell vlookup what column to look in (third pair of curly brackets).

So the first vlookup generated by this would be 

vlookup("BobMath",array,2,false)

where array contains

BobMath Cross (several other columns)...
JimMath Blue  (several other columns)...
...
BillHSIE (several other columns)... Hair

so it would pick up Cross

and the last vlookup would be

vlookup(BillHSIE,array,9,false)

which would pick up Hair.

Now it doesn't pick up a blank for Susan's maths because the first row it looks in is row 9 and it picks up Tickle from column E.

EDIT

If you then wanted to add some columns which only related to the student and not to any particular subject (like 'student attends counselling') you would need a different approach.

=ARRAYFORMULA(IFERROR(VLOOKUP($A21:$A24,FILTER({$C2:$C11,M2:M11},M2:M11<>""),2,FALSE),""))

This does a lookup as in (2) above but first skips any cells which are blank in the particular column being used for lookup. This formula only works on a single column, but can be pulled across for additional columns.