# Data transformation in Google Sheets

Because I am using Google Forms to collect data the source of data is not what I want so I need to create a tab and transform the data.

Here is an example of the Google Sheets (Exported from Google Forms):

``````Group Leader    Groupmate  Gender  Groupmate  Gender  Groupmate  Gender
Leader 1        John       M       Mary       F       Linda      F
Leader 2        KK         F       Johanna    F
``````

As you can see each row has a different number of `Groupmate`s. I would like to transform the table into:

``````Group Leader    Groupmate  Gender
``````

I believe `Groupmate` name is sort of unique so I think it is possible to merge several columns and then lookup the leader name by index-match.

• You really should post that last part as a separate question. We like questions to be as specific as possible. – Vidar S. Ramdal Mar 17 '17 at 9:42
• Sure I will post it in another thread. But just to keep my question here just to let people why the data is in that way. – Winston Mar 17 '17 at 9:45

You can achieve this by using `OFFSET` like so:

1. Enter formula in cells `B3:B5` which indicate how many values are present in each input row.
2. Enter formula in cells `A8:B13` (or further down as required) which will be used as the parameters in the next steps.
3. Enter `INDIRECT` formula in cells `C8:C13` - this uses `A8:A13` to grab the right Group Leader.
4. Enter `OFFSET` formulae in cells `D8:D13` and `E8:E13` to grab the groupmate and gender. ``````+------+--------------------------------------+--+
| Cell |               Formula                |  |
+------+--------------------------------------+--+
| B3   | =COUNTA(D3:3)                        |  |
| B4   | =COUNTA(D4:4)                        |  |
| B5   | =COUNTA(D5:5)                        |  |
|      |                                      |  |
| A8   | 1                                    |  |
| A9   | =IF(B8+1<INDIRECT("B"&2+A8),A8,A8+1) |  |
| A10  | =IF(B9+1<INDIRECT("B"&2+A9),A9,A9+1) |  |
| ...  | (drag down as required)              |  |
|      |                                      |  |
| B8   | 1                                    |  |
| B9   | =IF(B8+1<INDIRECT("B"&2+A8),B8+2,1)  |  |
| B10  | =IF(B9+1<INDIRECT("B"&2+A9),B9+2,1)  |  |
| ...  |                                      |  |
|      |                                      |  |
| C8   | =INDIRECT("C"&2+A8)                  |  |
| ...  |                                      |  |
|      |                                      |  |
| D8   | =OFFSET(\$C\$2,A8,B8)                  |  |
| ...  |                                      |  |
|      |                                      |  |
| E8   | =OFFSET(\$C\$2,A8,B8+1)                |  |
| ...  |                                      |  |
+------+--------------------------------------+--+
``````

Following up OP's Comment:

copy of formula provided by @Rubén:

``````=QUERY({"Group Leader","Groupmate","Gender";A2:C;A2:A,D2:E;A2:A,F2:G},"select * where Col3 <> '' order by Col1 asc")
``````

with my addition of `order by Col1 asc`.