Forum Discussion

office 365 office 365's avatar
office 365 office 365
Copper Contributor
Nov 16, 2018
Solved

DATA RESTRUCTURING USING EXCEL

Hi Excel Community,

i'm using Office 365.

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

 

Paper NumbSpeaker 1 NameSpeaker 1 CountrySpeaker 2 NameSpeaker 2 CountrySpeaker 3 NameSpeaker 3 CountryChair 1 NameChair 1 CountrChair 2 NameChair 2 Country
Paper 1AliMalaysiaBurhanChinaDaudBrazilDollahUAEZikriPakistan
Paper 2AbuMalaysiaBashahIndiaEmiliaUSFaridVietnamAdliPakistan
Paper 3ArifMalaysiaBrentUSEmySingaporeFuadIndiaHarunBrazil

 

 

i want to restructure above table to below data structure.

Paper NumbRoleNameCountry
Paper 1Speaker 1AliMalaysia
Paper 1Speaker 2BurhanChina
Paper 1Speaker 3DaudBrazil
Paper 1Chair 1DollahUAE
Paper 1Chair 2ZikriPakistan
Paper 2Speaker 1AbuMalaysia
Paper 2Speaker 2BashahIndia
Paper 2Speaker 3EmiliaUS
Paper 2Chair 1FaridVietnam
Paper 2Chair 2AdliPakistan
Paper 3Speaker 1ArifMalaysia
Paper 3Speaker 2BrentUS
Paper 3Speaker 3EmySingapore
Paper 3Chair 1FuadIndia
Paper 3Chair 2HarunBrazil

 

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

 

excel
Formulas and Functions
office 365
  • Jamil's avatar
    Jamil
    Nov 19, 2018

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

     

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

     

    Please see attached video.

     

     

10 Replies

Sort By
  • Jamil's avatar
    Jamil
    Bronze Contributor
    Nov 16, 2018

    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.

    • office 365 office 365's avatar
      office 365 office 365
      Copper Contributor
      Nov 19, 2018

      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. 

      • Jamil's avatar
        Jamil
        Bronze Contributor
        Nov 19, 2018
        You are welcome. Thanks for your feedback.
    • office 365 office 365's avatar
      office 365 office 365
      Copper Contributor
      Nov 19, 2018
      Amazing feedback from you guys. I will study base on your suggestion. Thank you very much
  • Jamil's avatar
    Jamil
    Bronze Contributor
    Nov 16, 2018

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

     

     

     

     

Resources