0

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
Leader 3        Rick       M

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

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

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.

Improve this question
2
  • 2
    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
2

Following up OP's Comment:

This is better: https://stackoverflow.com/questions/42869218/data-transformation-in-google-sheets

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.

Improve this answer
1

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.

enter image description here

+------+--------------------------------------+--+
| 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)                |  |
| ...  |                                      |  |
+------+--------------------------------------+--+
Improve this answer
1

Not the answer you're looking for? Browse other questions tagged or ask your own question.