May 31 2021 05:17 PM
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
May 31 2021 10:03 PM
@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.
Jun 01 2021 04:04 AM
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.
Jun 07 2021 03:37 PM
@Sergei Baklan thanks very much for taking the time to respond.
I am going to give your method a try.
Much appreciated,
Steve
Jun 07 2021 03:54 PM
@Riny_van_Eekelen thanks very much for your response.
I am attaching a sample file as you've requested.
Any further input would be very welcome.
Thanks again,
Steve
Jun 07 2021 03:55 PM
Jun 07 2021 10:31 PM
@stevep17 Something like in the attached file perhaps. First a bit of cleaning up in PQ, then some Power Pivot.
Jun 08 2021 12:36 AM
Jun 08 2021 02:07 AM
@stevep17 The easiest would be to connect to the file with the raw data, and then click "Refresh All" on the Data ribbon. That will connect to the data, transform it, load it into the data model, and update the pivot table. Personally I would not go so far as to write VBA code just to refresh.
But make sure that the data is in a structured table and clean, i.e. get rid of the empty column B, no merged cells, no fancy formatting needed.
The user doesn't need advanced skills to use such a schedule and the advantage it that the raw data is not touched/changed in any way. Since you have used PQ before, you'll know what I mean.