Forum Discussion

stevep17's avatar
stevep17
Copper Contributor
Jun 01, 2021

Report: grouped by rows & columns (is this possible?)

Greetings everyone - report builders especially,

I have a batch of survey responses (Answer Text) and I'd like to view them grouped by users (rows) & questions (column headings). Is this possible to achieve in Excel? Sample data attached.

All input welcome.

Thanks!

Steve

 

 

Charting
excel

8 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 01, 2021

    stevep17 

    If with legacy formulas for such model

    in F3

    =IFERROR(
  INDEX($B$3:$B$22,
        AGGREGATE(15,6,
            1/(COUNTIF($F$2:F2,$B$3:$B$22)=0)/
              ($B$3:$B$22<>"")*
            (ROW($B$3:$B$22)-ROW($B$2)),1)),
"")

    drag down till empty cells appear

    in G2

    =IFERROR(
  INDEX($C$3:$C$22,
    AGGREGATE(15,6,1/(COUNTIF($F$2:F$2,$C$3:$C$22)=0)*
        (ROW($B$3:$B$22)-ROW($B$2)),1)),
"")

    drag to the right

    in G3

    =IFERROR(
   INDEX($D$3:$D$22,
     MATCH(G$2,
        INDEX($C$3:$C$22,MATCH($F3,$B$3:$B$22,0)):
        INDEX($C$3:$C$22,ROWS($C$3:$C$22)),0)+
     MATCH($F3,$B$3:$B$22,0)-1),
"")

    drag down and to the right.

    • stevep17's avatar
      stevep17
      Copper Contributor
      Jun 07, 2021

      SergeiBaklan thanks very much for taking the time to respond. 

      I am going to give your method a try.

      Much appreciated,

      Steve

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 01, 2021

    stevep17 "Is this possible to achieve in Excel?" The answer is Yes!

    How exactly is a bit difficult to explain in words, without some real data to play with. From looking at the pictures, I would first connect to and transform the data a bit with Power Query, then load the data into the Data Model. Then you need som DAX code that allows the text based answers to be entered in the value field of a pivot table.

    Now, this may sound all very alien, if you have never seen or heard of this before. And if you are on a Mac, you can forget it altogether. So, if you can provide a file with some real, but anonymous data, I or someone else can get you started.

Resources