auto re arrange data

[amirkharkongor]

is there any solution / formula to re arrange the following example :

My sheet
name	age	gender	name	age	gender	name	age	gender
aaa	15	M	bbb	16	M	ccc	14	F
ddd	13	F	eee	14	M	fff	17	M
Formula to rearrange in the following manner
name	age	gender
aaa	15	M
bbb	16	M
ccc	14	F
ddd	13	F
eee	14	M
fff	17	M

 

[awoohaw]

I'm assuming this is sample data. How many 3 column wide sections does your actual data have?
This can probably be done much easier with 365 (using LET, FILTER, and LAMBDA Functionality ) than with the functions you have in 2019.
So, knowing the scale of your question could be important in creating a scalable solution.

 

[amirkharkongor]

I'm assuming this is sample data. How many 3 column wide sections does your actual data have?
This can probably be done much easier with 365 (using LET, FILTER, and LAMBDA Functionality ) than with the functions you have in 2019.
So, knowing the scale of your question could be important in creating a scalable solution.

The data sheet is generated from google form (with 6 sections) that I have created.

 

[JEC]

Here a PowerQuery solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    headers = List.FirstN(Table.ColumnNames(Source),3),
    data = List.Combine(Table.ToRows(Source)),
    lName = List.Alternate(data,2,1,1),
    lGender = List.Alternate(data,2,1),
    lAge = List.RemoveItems(data,lName & lGender),
    lCombined = Table.FromRows(List.Zip({lName,lAge,lGender}),headers)
in
    lCombined

 

[amirkharkongor]

Thank you so much sir.

But while trying another problem comes up. Kindly help me how to re arrange the following. I am really new to Excel formulas please help me

aaa	English	Maths	History
bbb	Geography	Science	English
ccc	English	Science	Maths

to

aaa	English
aaa	Maths
aaa	History
bbb	Geography
bbb	Science
bbb	English
ccc	English
ccc	Science
ccc	Maths

 

[JEC]

Simply unpivot other columns(one click in PQ)