Forum Discussion
Solved
Combine/Rearrange Rows and Columns
Hi, I am trying to combine and rearrange two columns of data into a different format. Would like the User column to become the header and the Food column to populate under the corresponding user...
For such layout
if you are on Excel with Dynamic Array that could be
in E2
=TRANSPOSE(UNIQUE(B3:B9))in E3
=FILTER($C$3:$C$9,$B$3:$B$9=E$2)and drag to the right.
Another option with more traditional functions
In I2
=IFERROR(INDEX($B$3:$B$9,AGGREGATE(15,6,1/(COUNTIF($H$2:H2,$B$3:$B$9)=0)*(ROW($B$3:$B$9)-ROW($B$2)),1)),"")in I3
=IFERROR(INDEX($C$3:$C$9,AGGREGATE(15,6,1/(COUNTIF(I$2:I2,$C$3:$C$9)=0)/($B$3:$B$9=I$2)*(ROW($C$3:$C$9)-ROW($C$2)),1)),"")and drag to the right and down.
Power Query also could be an option.
Just in case, added Power Query
Generated script:
let
Source = Excel.CurrentWorkbook(),
SelectRange = Table.SelectRows(Source, each ([Name] = "Range")),
GetRange = SelectRange{[Name="Range"]}[Content],
AddHeaders = Table.PromoteHeaders(
GetRange,
[PromoteAllScalars=true]
),
GroupByFirstColumn = Table.Group(
AddHeaders,
{"U"},
{{"AllRows", each _[F]}}
),
FinalTable = Table.FromColumns(
GroupByFirstColumn[AllRows],
GroupByFirstColumn[U]
)
in
FinalTable