I am trying to get data in google sheets from one sheet to another and manipulate the received data on the new sheet

Question

I have a sheet from a forms survey. The data in the sheet has the following headers:

B : Name1,
C : Name1_Grade,
D : Name1_Shoe_Size,
E : Name2,
F : Name2_Grade,
G : Name2_Shoe_Size,
H : Name3,
I : Nanme3_Grade,
J : Name3_Shoe_Size

The response form sheet has much more data in it. So, I use a query to get this data into a new sheet, =query('Data !B2:J150,"Select B, C, D, E, F, G, H, I where (C=4 OR F=4 OR I=4)".

This gets my data into the new sheet, however, once it's in the sheet I need to move all of the Names, grades and shoe size to 3 columns so the end result, once the data is on the new sheet, would look like:

Names   |  Grade | Shoe_Size 
John F  |  4     |  6.5

The formula above works, however, I need to get all the names, grades, and shoe sizes into one column. Not everyone has multiple entries. So the formula needs to be able to check if something is in the other columns. The form that generates the data is set up so that one person can fill out the form for a whole family with up to 5 kids.

Is there an easy way to accomplish this in one formula?

Here is a link to a sample spreadsheet: https://docs.google.com/spreadsheets/d/1P9qgh0o3gjDikuaX75bBr9RyYz9ofxedAuJy0tXuRko/edit?usp=sharing

Answer 1

The trick is to stack the columns on top of each other. So that the data lines up column-wise.

You can do that by using braces with semicolons, like so:

{B2:D;E2:G;H2:J}

Then as player0 said, you can use Query(). Or you could try using the Filter():

=filter({B2:D;E2:G;H2:J},{C2:C;F2:F;I2:I}=4)