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

 

Thanks

5 Replies

@Yoan_Tufel 

Power Query could work:

image.png

Script is

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

i will try as soon as possible

@Sergei Baklan 

@Yoan_Tufel 

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 ✌

@Yoan_Tufel 

If for any Excel here

image.png

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).