Forum Discussion

iver band's avatar
iver band
Copper Contributor
Aug 30, 2017
Solved

Transforming rows and columns to a sequence of rows

I have a largely empty spreadsheet that looks something like this:                       Col1Label Col2Label Col3Label ... Row1Label Row2Label Row3Label Row4Label ... I want to transform it in...
excel
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    Aug 31, 2017

    Hello,

    Power Query can be used for that. First, copy all column labels into all rows, like this:

     

     

    Then load the table into Power Query (called Get&Transform) in Excel 2016. Select Column 1 and then click Unpivot other columns. Combine the two columns you want to keep and delete the rest. The result looks like this:

     

     

     

     

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Sep 01, 2017

If anyone else is viewing this post and they aren't familiar with the easy method (Power Query).  It's also possible to accomplish this with workbook formulas.

 

=IF(ISBLANK(INDIRECT("Sheet1!A"&(INT((ROW(D1)-1)/3)+1)+1)),"",INDIRECT("Sheet1!A"&(INT((ROW(D1)-1)/3)+1)+1))&IF(ISBLANK(INDIRECT("Sheet1!"&CHAR(65+ROW())&1)),INDIRECT("Sheet2!B"&(ROWS($B$1:B1)-3)),INDIRECT("Sheet1!"&CHAR(65+ROW())&1))

 

WHERE 3 = COLUMN HEADER COUNT

THIS COULD EASILY BE REPLACED WITH COUNTA()....

 

See attached workbook for an example.

Resources