Thursday, February 7, 2013

yogi_CrossTab Judges Scores By Contestant From A List

                                          Google Spreadsheet   Post  #1019
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Feb 07, 2013
post by user: Timothy Green said:(!category-topic/docs/spreadsheets/X_0GmKlRUhE)
Best way to re-organize data generated by a form?

I'm trying to generate a score report for an Essay Contest, and am very new at formulas/functions/data sorting.

Each contestant had their essays read by three different judges.  The judges selected "Yes" or "No" to the question - "should this essay move on to the next round of judging?"
So, each essay should get three "Yes" or "No" scores - and I need to find out a way to display their scores so my supervisor can quickly tell who's moving on to the next round and who isn't.

The problem is that the spreadsheet has been populated by the form with these Columns - A: "Contestant Name", B: "Judge's Name", C: "Score". 
So I have a report something like this:
  A                B               C
Carl            Jane         Yes
Cassie        Jane         No
Connie        John        No
Caleb          Jim          Yes
Cam           Joe           No
Cassie        John         No
Caleb          Jill            Yes
Caleb          John         No
Cassie        Jill             No
Cam           Jim            Yes
Cam           John          No
Carl            John          No
Carl            Jill              Yes
Callie          Joe            No
Is there a formula that will allow me to quickly count up and organize the data to see each Contestant with their scores in the same Row?

For example, can I somehow transpose the data from these columns into columns like this?
A: "Contestant"  B: "Judge 1" C: "Judge 1's Score"  D: "Judge 2"  E: "Judge 2's Score"  F: "Judge 3"  D: "Judge 3's score"
Many thanks for any help you can offer...
following is a solution to the problem