Transforming Google Form Data with ARRAYFORMULA in Google Sheets

In my job, I often need to assign tasks to reviewers. For example, I may assign faculty to review and score student work for assessment purposes, or reviewers to score assessment reports or award applications. Using Google Forms to collect reviewers' feedback and scores is my go-to low-cost method. 

Below is a typical Google Form setup that I use:

Question 1: Review Name [Drop Down Menu]

Question 2: Report Reviewed [Drop Down Menu]

The response data returns a vertical dataset like the mock dataset below.

🧪 Mock Dataset (Raw Form Responses)

Sheet name: Form Responses

Column A Timestamp	Column B Reviewer Name	Column C Report Reviewed
2025/03/20 09:01	Alice	Report A
2025/03/20 09:03	Bob	Report B
2025/03/20 09:05	Alice	Report C
2025/03/20 09:08	Alice	Report D
2025/03/20 09:10	Bob	Report E
2025/03/20 09:12	Carol	Report A
2025/03/20 09:14	Carol	Report B

In order for me to monitor whether a reviewer has completed their reviews, I need to transform this vertical dataset into a horizontal dataset like below:

🎯 Goal: Reformat into a horizontal dataset

Reviewer Name	Report 1	Report 2	Report 3
Alice	Report A	Report C	Report D
Bob	Report B	Report E
Carol	Report A	Report B

Today, I tried ARRAYFORMULA and FILTER in Google Sheets to restructure the dataset from long and vertical to clean, horizontal, and readable.

Below are the steps written 

🔧 Step-by-Step Guide (Written by ChatGPT and verified by the author)

1. List Unique Reviewer Names In a new sheet, type the following into cell A2:

=UNIQUE('Form Responses'!B2:B)

2. Label Report Columns Add the headers Report 1, Report 2, Report 3, etc., in cells B1, C1, and D1.

3. Use ARRAYFORMULA with FILTER and TRANSPOSE In cell B2, type the following formula:

=TRANSPOSE(FILTER('Form Responses'!C$2:C, 'Form Responses'!B$2:B = A2))

This formula says: In the range  'Form Responses'!B$2:B, if the value equals to A2 (the review name), find all the reports in the range 'Form Responses'!C$2:C associated with with A2 through the FILTER function. Flip this list of reports horizontally using the TRANSPOSE function. 

Then, drag this down for other reviewers.

As the responses come in, the track sheet should automatically update. This formula provides time-saving tracking of reviewer assignment completion! 

(This poster was co-written with ChatGPT)