Promoting one column to become headers whilst keeping data paired

[Milos]

Hi all,

Hi there,

Would anybody know how I best attempt this transposing task? VBA or power query?. I need to promote column B to become headers, but the data in column A and C are paired with one another.

Data in raw format:

Column A​	Column B​	Column C​
Type	Category	Result
ABC	Address	49 Joe Blogs Street
ABC	Phone Number	027JOEBLOGS
ABC	Email	joe.bloggs@gmail.com
BCD	Address	83 Jenny Blogs Street
BCD	Phone Number	027JENNYBlOGS
BCD	Planned Trip	1 March 2020
CDE	Address	15 No Idea Lane
CDE	Phone Number	021NOIDEA

Data in required format:

Column A​	Column B​	Column C​	Column D​	Column E​
Type	Address	Phone Number	Email	Planned Trip
ABC	49 Joe Blogs Street	027JOEBLOGS	joe.bloggs@gmail.com
BCD	83 Jenny Blogs Street	027JENNYBlOGS
CDE	15 No Idea Lane	021NOIDEA		1 March 2020

Any help with putting me in the right direction is greatly appreciated!

Cheers,
Milos

 

[Norie]

Milos

In Power Query you could delete the 'header' row and then pivot on column B.

When pivoting choose column C for values and select Don't aggregrate for the Aggregrate Value Function under Advanced options.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Pivoted Column" = Table.Pivot(#"Removed Top Rows", List.Distinct(#"Removed Top Rows"[#"Column B"]), "Column B", "Column C")
in
    #"Pivoted Column"

 

[Milos]

You are a champion thanks Norie. I was playing around in Power Query but the 'Don't aggregate' section was where I stumbled! Thanks!