Google Spreadsheet combining rows into new sheet

Question

I'm using google forms to collect student information and gather it into a spreadsheet. The form will collect scores and feedback from two raters for a series of questions for each student. So, each student will have two rows per question (one for each rater's form data). It will look something like this:

https://docs.google.com/spreadsheet/ccc?key=0AvImhr5O8tEddG1hMDdlMXNkWnFLZTd3UE9OWjRMaVE&usp=sharing

I'd like to be able to combine the information into one row on a separate sheet (see sheet 2). I'm wondering if there is a function or script or add-on that will search to find the matching rows (Student, Question, Retake, Rater1/2) and copy that information into a new sheet, followed by the score and feedback information as well. I'd like all the data from both raters to be in one row (per student/question).

Is there a way to do that?

My ultimate goal is to use the Autocrat add-on to merge the information into a PDF and have it emailed.

Answer 1

This can be done with a few standard formulas.

First, in the Combined sheet, you can fill the ID and Name fields simply by referencing the Contact sheet, like this: =Contact!B2 for ID and =Contact!B3 for Name.

Next, for the Question 1: Behavior field, for example, you'll use a formula like this:

=INDEX(Scores!$A$2:$H$11,MATCH(1,(Scores!$C$2:$C$11=$B2)*(Scores!$D$2:$D$11=$E$1),0),4)

I'll do my best to break that down for you... from left to right:

• Scores!$A$2:$H$11 references the all the values in the Scores sheet. You'll only have to change $H$11 if the size of that sheet changes, or you can just set it to a really big value from the get go.
• Scores!$C$2:$C$11 references the ID field in the Scores sheet. Again, you'll only have to change $C$11 if the size of that sheet changes.
• $B2 References the ID field on current row in the Combined sheet. The number should match whatever row you're on, but Sheets should change that for you automatically if you copy and paste the formula down a column.
• Scores!$D$2:$D$11 references the Question field in the Scores sheet. Like before, you'll only have to change $D$11 if the size of that sheet changes.
• $E$1 references the field name, "Question 1: Behavior", in the Combined sheet. You'll have to change this to $M$1 for all of the "Q2" fields.
• The 4 at the end of the formula determines which field from the Scores sheet is inserted in this cell. The Question field is the fourth from the left, hence 4. When you insert the Retake field, you change the number to 5, etc.

I hope this doesn't seem too tedious or convoluted because it's actually pretty straightforward. After you get the formula working for the first field, it'll take you less than ten minutes to do all the others.