Forum Discussion

Rob Nunley's avatar
Rob Nunley
Brass Contributor
Mar 28, 2020
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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 28, 2020

    Rob Nunley 

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 28, 2020

Rob Nunley 

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.

Resources