Transpose column header and replicate the rows

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
354
Office Version
  1. 365
Platform
  1. Windows
source data contains more than 1000 lines. it will takes a while if it is transposed 1by1.. is there any formula that can transpose the data to transform to expected result?.. thank you


Name-List-for-Practice.xlsm
ABCDEFGHIJKLMNO
1source dataexpected result
2namecodeyear 1year 2year 3year 4year 1ABCXX12
3ABCXX12358year 2ABCXX13
4DEFXX25135year 3ABCXX15
5GHIXX32362year 4ABCXX18
6year 1DEFXX25
7year 2DEFXX21
8year 3DEFXX23
9year 4DEFXX25
10year 1GHIXX32
11year 2GHIXX33
12year 3GHIXX36
13year 4GHIXX32
14
15
Sheet25
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
How about
Fluff.xlsm
ABCDEFGHIJKLMN
1source dataexpected result
2namecodeyear 1year 2year 3year 4year 1ABCXX12
3ABCXX12358year 2ABCXX13
4DEFXX25135year 3ABCXX15
5GHIXX32362year 4ABCXX18
6year 1DEFXX25
7year 2DEFXX21
8year 3DEFXX23
9year 4DEFXX25
10year 1GHIXX32
11year 2GHIXX33
12year 3GHIXX36
13year 4GHIXX32
Data
Cell Formulas
RangeFormula
K2:N13K2=LET(d,C3:F5,HSTACK(TOCOL(IF(d<>"",C2:F2,1/0),2),TOCOL(IF(d<>"",A3:A5,1/0),2),TOCOL(IF(d<>"",B3:B5,1/0),2),TOCOL(d,1)))
Dynamic array formulas.
 
Upvote 0
Solution
an alternative is with Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute]= "name" then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Attribute]= "code" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom", "Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> "code" and [Attribute] <> "name")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Attribute", "Custom", "Custom.1", "Value"})
in
    #"Reordered Columns"
 
Upvote 0
How about
Fluff.xlsm
ABCDEFGHIJKLMN
1source dataexpected result
2namecodeyear 1year 2year 3year 4year 1ABCXX12
3ABCXX12358year 2ABCXX13
4DEFXX25135year 3ABCXX15
5GHIXX32362year 4ABCXX18
6year 1DEFXX25
7year 2DEFXX21
8year 3DEFXX23
9year 4DEFXX25
10year 1GHIXX32
11year 2GHIXX33
12year 3GHIXX36
13year 4GHIXX32
Data
Cell Formulas
RangeFormula
K2:N13K2=LET(d,C3:F5,HSTACK(TOCOL(IF(d<>"",C2:F2,1/0),2),TOCOL(IF(d<>"",A3:A5,1/0),2),TOCOL(IF(d<>"",B3:B5,1/0),2),TOCOL(d,1)))
Dynamic array formulas.
thanks man, it works..
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,226,735
Messages
6,192,733
Members
453,752
Latest member
Austin2222

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top