Change data structure

New Contributor

Hello, I will try to be clear sorry if am not


Have I data that is structure like such:

COLUMN A            B

1:phone        |  customer info

2: email         | custumer info

3: phone       |  custumer info

4: email        |  custumer info



I would like to know how I can restructure it as follow 


email   phone


info      info

info      info



5 Replies


Power Query could work:


Script is

    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Info", each _[Column2]}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[Info], #"Grouped Rows"[Column1])

i will try as soon as possible

@Sergei Baklan 


A formula solution. Please see attached workbook.

to use SEQUENCE function for row number in INDEX function looks good
Because my colleagues don't have O365, i am trying to create solutions to let them use my solutions. There are many different approaches for dynamic arrays. Thanks for sharing that kind of usage ✌


If for any Excel here


in G2

=IFERROR( INDEX( $B:$B,  AGGREGATE(15,6,1/($A:$A=G$1)*( ROW($A:$A) - ROW($A$1) +1 ), ROW()-ROW($G$1) ) ), "")

and drag it to the right and down till cells with empty text appear. The only it's much better to take some range instead of entire columns (or table or dynamic range).