Transpose column header and replicate the rows

[ExcelNewbie2020]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	source data										expected result
2	name	code	year 1	year 2	year 3	year 4					year 1	ABC	XX1	2
3	ABC	XX1	2	3	5	8					year 2	ABC	XX1	3
4	DEF	XX2	5	1	3	5					year 3	ABC	XX1	5
5	GHI	XX3	2	3	6	2					year 4	ABC	XX1	8
6											year 1	DEF	XX2	5
7											year 2	DEF	XX2	1
8											year 3	DEF	XX2	3
9											year 4	DEF	XX2	5
10											year 1	GHI	XX3	2
11											year 2	GHI	XX3	3
12											year 3	GHI	XX3	6
13											year 4	GHI	XX3	2
14
15
Sheet25

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	source data										expected result
2	name	code	year 1	year 2	year 3	year 4					year 1	ABC	XX1	2
3	ABC	XX1	2	3	5	8					year 2	ABC	XX1	3
4	DEF	XX2	5	1	3	5					year 3	ABC	XX1	5
5	GHI	XX3	2	3	6	2					year 4	ABC	XX1	8
6											year 1	DEF	XX2	5
7											year 2	DEF	XX2	1
8											year 3	DEF	XX2	3
9											year 4	DEF	XX2	5
10											year 1	GHI	XX3	2
11											year 2	GHI	XX3	3
12											year 3	GHI	XX3	6
13											year 4	GHI	XX3	2
Data

Cell Formulas
Range	Formula
K2:N13	K2	=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.

 

[alansidman]

an alternative is with 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"

 

[ExcelNewbie2020]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	source data										expected result
2	name	code	year 1	year 2	year 3	year 4					year 1	ABC	XX1	2
3	ABC	XX1	2	3	5	8					year 2	ABC	XX1	3
4	DEF	XX2	5	1	3	5					year 3	ABC	XX1	5
5	GHI	XX3	2	3	6	2					year 4	ABC	XX1	8
6											year 1	DEF	XX2	5
7											year 2	DEF	XX2	1
8											year 3	DEF	XX2	3
9											year 4	DEF	XX2	5
10											year 1	GHI	XX3	2
11											year 2	GHI	XX3	3
12											year 3	GHI	XX3	6
13											year 4	GHI	XX3	2
Data

Cell Formulas
Range	Formula
K2:N13	K2	=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..

 

[Fluff]

Glad we could help & thanks for the feedback.