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 Numb Speaker 1 Name Speaker 1 Country Speaker 2 Name Speaker 2 Country Spe...
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.

     

     

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