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
    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
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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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