Pivot table solution

[antinora]

Greetings:

My school is having High School Parent Teacher Conferences remotely. As our NYC school is closed there isn't someone at the main switchboard to take calls from parent to schedule an appointment.

I'm attempting to create a scheduling solution for parents using a Google form. A parent will enter student bio data (including Student ID as unique identifier) and select, from a dropdown, a teacher for each content area (English, Math, etc...).

Here is link to draft form: Parent Teacher Conference Registration - Spring 2021

From the spreadsheet, I need to get to each teacher the names of their students for which a parent wants to receive a phone call during the conference.

I've a attached a screen shot of the data. How can I scan across each row then pull the student Bio data for each teacher?

Thank you in advance.

I'm familiar with and have used VBA in the past. I'm just a little rusty.

John

 

[mart37]

You can use Power Query to unpivot your table.
Define your table as an Excel Table (Format as Table)
Goto Data - From Table
Select the columns with teacher names.
Goto Transform - All Columns - Unpivot for Columns
Rename the column Value to Teacher
Goto File - Close and Load

In a pivot table you can filter on a teacher.