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 Groupmate
s. 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.