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