Macro to take list of names make them headers and creat list of sub names in each colum

[tonywatsonhelp]

Hi Everyone,

Below is an example of what i currently have and what i want.

So In column A I have a list of Header Names, the names repeat for every row from 2 till last row, each header can have any amount in it so one kight only have two headers, another might be 15-25 or more.
I want to take this list and for each unique Name create a column header starting at AA of same sheet.
the column b, list all the sub headers,
I'd like to take these and add them to the columns there headers are in as in example "What I want"

please help if you can

Thanks

Tony

What i want

	AA	AB	AC	AD	AE	AF
1	Name1	Name2	Name3	Name4	Name5
2	Sub a1	Sub b1	Sub c1	Sub d1	Sub e1
3	Sub a2	Sub b2	Sub c2	Sub d2	Sub e2
4	Sub a3		Sub c3	Sub d3
5				Sub d4
6
7
8
9

(What I Have)

	A	B	C
1	Header Names	Sub Names
2	Name1	Sub a1
3	Name1	Sub a2
4	Name1	Sub a3
5	Name2	Sub b1
6	Name2	Sub b2
7	Name3	Sub c1
8	Name3	Sub c2
9	Name3	Sub c3
10	Name4	Sub d1
11	Name4	Sub d2
12	Name4	Sub d3
13	Name4	Sub d4
14	Name5	Sub e1
15	Name5	Sub e2
TILL END OF DATA

 

[alansidman]

Employing Power Query/Get and Transform, this task can be accomplished with the Mcode that follows. Use the Unpivot function within PQ.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name1", type text}, {"Name2", type text}, {"Name3", type text}, {"Name4", type text}, {"Name5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

 

[tonywatsonhelp]

This is amazing!
thank you alan