VBA macro to convert partial horizontal data to vertical

[MBD]

Hi All,

Hoping someone can help me with this - I've tried searching the forum for the solution, but none quite fits what I'm looking to do.

Essentially, I'm looking to transform excel data as per below illustration:
- create a new column called 'Project ID', which is a combination of Project Number and Stage 1 - 4
- for every stages, repeat Project Number, Project Name, Team and Work Site


sheet1 (original):

Project Number	Project Name	Team	Work Site	Stage 1	Stage 2	Stage 3	Stage 4
1​	Project 1	Study	New York	a	b	c	d
2​	Project 2	Study	Lost Angeles	a	b	c	d
3​	Project 3	Marketing	Lost Angeles	a	b	c	d

Into sheet2 (transformed):

Project ID	Project Number	Project Name	Team	Work Site
1.a	1​	Project 1	Study	New York
1.b	1​	Project 1	Study	New York
1.c	1​	Project 1	Study	New York
2.a	2​	Project 2	Study	Lost Angeles
2.b	2​	Project 2	Study	Lost Angeles
2.c	2​	Project 2	Study	Lost Angeles
3.a	3​	Project 3	Marketing	Lost Angeles
3.b	3​	Project 3	Marketing	Lost Angeles
3.c	3​	Project 3	Marketing	Lost Angeles

Thank you for your help.

 

[Kerryx]

You could use Power query to help with this

Book1
	A	B	C	D	E	F	G	H
1	Project Number	Project Name	Team	Work Site	Stage 1	Stage 2	Stage 3	Stage 4
2	1	Project 1	Study	New York	a	b	c	d
3	2	Project 2	Study	Lost Angeles	a	b	c	d
4	3	Project 3	Marketing	Lost Angeles	a	b	c	d
5
6	Project ID	Project Number	Project Name	Team	Work Site
7	1.a	1	Project 1	Study	New York
8	1.b	1	Project 1	Study	New York
9	1.c	1	Project 1	Study	New York
10	1.d	1	Project 1	Study	New York
11	2.a	2	Project 2	Study	Lost Angeles
12	2.b	2	Project 2	Study	Lost Angeles
13	2.c	2	Project 2	Study	Lost Angeles
14	2.d	2	Project 2	Study	Lost Angeles
15	3.a	3	Project 3	Marketing	Lost Angeles
16	3.b	3	Project 3	Marketing	Lost Angeles
17	3.c	3	Project 3	Marketing	Lost Angeles
18	3.d	3	Project 3	Marketing	Lost Angeles
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", type text}, {"Project Name", type text}, {"Team", type text}, {"Work Site", type text}, {"Stage 1", type text}, {"Stage 2", type text}, {"Stage 3", type text}, {"Stage 4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Number", "Project Name", "Team", "Work Site"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Project ID", each [Project Number]&"."&[Value]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Project ID", "Project Number", "Project Name", "Team", "Work Site", "Attribute", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute", "Value"})
in
    #"Removed Columns"

 

[MBD]

You could use Power query to help with this

Book1
	A	B	C	D	E	F	G	H
1	Project Number	Project Name	Team	Work Site	Stage 1	Stage 2	Stage 3	Stage 4
2	1	Project 1	Study	New York	a	b	c	d
3	2	Project 2	Study	Lost Angeles	a	b	c	d
4	3	Project 3	Marketing	Lost Angeles	a	b	c	d
5
6	Project ID	Project Number	Project Name	Team	Work Site
7	1.a	1	Project 1	Study	New York
8	1.b	1	Project 1	Study	New York
9	1.c	1	Project 1	Study	New York
10	1.d	1	Project 1	Study	New York
11	2.a	2	Project 2	Study	Lost Angeles
12	2.b	2	Project 2	Study	Lost Angeles
13	2.c	2	Project 2	Study	Lost Angeles
14	2.d	2	Project 2	Study	Lost Angeles
15	3.a	3	Project 3	Marketing	Lost Angeles
16	3.b	3	Project 3	Marketing	Lost Angeles
17	3.c	3	Project 3	Marketing	Lost Angeles
18	3.d	3	Project 3	Marketing	Lost Angeles
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Number", type text}, {"Project Name", type text}, {"Team", type text}, {"Work Site", type text}, {"Stage 1", type text}, {"Stage 2", type text}, {"Stage 3", type text}, {"Stage 4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Number", "Project Name", "Team", "Work Site"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Project ID", each [Project Number]&"."&[Value]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Project ID", "Project Number", "Project Name", "Team", "Work Site", "Attribute", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute", "Value"})
in
    #"Removed Columns"

View attachment 71253

Hi Kerryx, thank you so much - this is brilliant!
Don't know why I didn't think of Power Query!

 

[Kerryx]