DATA RESTRUCTURING USING EXCEL

[office 365 office 365]

Hi Excel Community,

i'm using Office 365.

i want to manage my data using excel. below is my sample data.

Paper Numb	Speaker 1 Name	Speaker 1 Country	Speaker 2 Name	Speaker 2 Country	Speaker 3 Name	Speaker 3 Country	Chair 1 Name	Chair 1 Countr	Chair 2 Name	Chair 2 Country
Paper 1	Ali	Malaysia	Burhan	China	Daud	Brazil	Dollah	UAE	Zikri	Pakistan
Paper 2	Abu	Malaysia	Bashah	India	Emilia	US	Farid	Vietnam	Adli	Pakistan
Paper 3	Arif	Malaysia	Brent	US	Emy	Singapore	Fuad	India	Harun	Brazil

i want to restructure above table to below data structure.

Paper Numb	Role	Name	Country
Paper 1	Speaker 1	Ali	Malaysia
Paper 1	Speaker 2	Burhan	China
Paper 1	Speaker 3	Daud	Brazil
Paper 1	Chair 1	Dollah	UAE
Paper 1	Chair 2	Zikri	Pakistan
Paper 2	Speaker 1	Abu	Malaysia
Paper 2	Speaker 2	Bashah	India
Paper 2	Speaker 3	Emilia	US
Paper 2	Chair 1	Farid	Vietnam
Paper 2	Chair 2	Adli	Pakistan
Paper 3	Speaker 1	Arif	Malaysia
Paper 3	Speaker 2	Brent	US
Paper 3	Speaker 3	Emy	Singapore
Paper 3	Chair 1	Fuad	India
Paper 3	Chair 2	Harun	Brazil

Appreciate if you can help me how to restructure my sample data using excel. thank you

[#2]

You can use three formulas to unwind your data as you described.

see attached workbook example and the video that shows how you can do that.

First formula 

=INDEX($F$2:$F$4,INT((ROWS($B$8:$B8)-1)/5)+1)

Second formula

=SUBSTITUTE(SUBSTITUTE(INDEX($G$1:$P$1,1,(MOD((ROWS($B$8:B8)-1),5)+1)*2),"Country",""),"Name","")

Third Formula

=INDEX($G$2:$P$4,MATCH($A8,$F$2:$F$4,0),MATCH($B8&C$7,$G$1:$P$1,0))

[#3]

Hi

First unpivot, then pivot.

Done with Power Query.

[#4]

from Detlef's attachment you can see that it can be done using power query and If you could not figures it out from the applied steps in PQ and you are wondering how he has done it.  Attached is the video that demonstrates how it is done in Power Query.

[#5]

Amazing feedback from you guys. I will study base on your suggestion. Thank you very much

[#6]

Amazing feedback from you guys. I will study base on your suggestion. Thank you very much

[#7]

Thanks a lot Jamil. its working. i'm really appreciate your effort to make tutorial video for me. may you successful in your life journey. 

[#8]

You are welcome. Thanks for your feedback.

[#9]

السلام عليكم

Dear Jamil.

i having another problem to manage multiple column to restructure format database.

please find the attachment for your reference. 

for your information, 

below details is one variable (Role)

Organizer
Chair
Discussant 1
Discussant 2
Speaker 1
Speaker 2
Speaker 3
Speaker 4
Speaker 5
Speaker 6
Speaker 7
Speaker 8
Speaker 9

appreciate if you can help me 

[#10 · Accepted solution]

وعليكم السلام

I have recorded a video for you that demonstrates the use of power query to transform your data.

Please see attached video.

[#11]

Thank you for your superb video:)