Change data structure

Question

Hello, I will try to be clear sorry if am not&nbsp;Have I data that is structure like such:COLUMN A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B1:phone&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; customer info2: email&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| custumer info3: phone&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; custumer info4: email&nbsp; &nbsp; &nbsp; &nbsp; |&nbsp;&nbsp;custumer info&nbsp;&nbsp;I would like to know how I can restructure it as follow&nbsp;&nbsp;email&nbsp; &nbsp;phone----------------info&nbsp; &nbsp; &nbsp; infoinfo&nbsp; &nbsp; &nbsp; info&nbsp;Thanks

patrick2788 · Answer

Yoan_Tufel&nbsp;A formula solution. Please see attached workbook.

sekoleyte · Answer

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 ✌

sergeibaklan · Answer

Yoan_Tufel&nbsp;
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).

sergeibaklan · Answer

Yoan_Tufel&nbsp;
Power Query could work:

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

yoan_tufel · Answer

i will try as soon as possibleSergeiBaklan&nbsp;

Forum Discussion

Change data structure

Share

Resources