I'm collecting student registrations via Google Forms into a Google Sheet, but I'm having trouble reformatting it to go into my master student list. I'd like to be able to convert each row in the auto-generated response form into multiple rows on the master student list, but have them share some of the same data. In this case the parent information.
Example data where there are up to 3 more student sections in the row:
| Parent Name | Parent Cell # | Primary Email | Student Name | DOB | Student Name | DOB |
|---|---|---|---|---|---|---|
| Momma Park | (123)456-7890 | parkfamily@gmail | Susie Park | 1/1/2000 | Amy Park | 3/9/2002 |
| Momma Stevens | (123)456-7890 | stevensfamily@gmail | John Stevens | 4/2/2001 | Ryan Stevens | 8/15/2004 |
Using Query and Importrange I'm able to grab all of the student information into a new sheet by selecting just those columns, but since the nature of nested Importranges is that they're sequential imports, students who have the same parents end up not being grouped up like so:
| Student Name | DOB |
|---|---|
| Susie Park | 1/1/2000 |
| John Stevens | 4/2/2001 |
| Amy Park | 3/9/2002 |
| Ryan Stevens | 8/15/2004 |
Additionally, I can't grab the parent information and append it to each imported student row.
Ideally I would want it to look like this:
| Student Name | DOB | Parent Name | Parent Cell # | Primary Email |
|---|---|---|---|---|
| Susie Park | 1/1/2000 | Momma Park | (123)456-7890 | parkfamily@gmail |
| Amy Park | 3/9/2002 | Momma Park | (123)456-7890 | parkfamily@gmail |
| John Stevens | 4/2/2001 | Momma Stevens | (123)456-7890 | stevensfamily@gmail |
| Ryan Stevens | 8/15/2004 | Momma Stevens | (123)456-7890 | stevensfamily@gmail |
But I'd still be happy if I can just append the parent information to the second table.
I'm pretty stumped. I would love to hear suggestions on how I can modify my formulas, but unfortunately since we've already started registrations I can't modify the Google Form and the example data format is the final format for this year.
