How can I make an Excel Pivot Table Value Field not perform any operation?

2
votes

I am trying to reorganize an Excel table with the columns "User ID," "Question" and "Response"

here is a picture with the first few rows

so that each column is one of the "Question" values, each row is one of the "User ID" values, and the values are the "Responses."

I have gotten really close by using a Pivot Table - the columns and rows are right, but I can't get the Pivot Table tool to display the responses. I have to select an aggregating function (e.g., "Sum" or "Product") which causes the responses to display as 0s or 1s

here is an example of the first few rows and columns

Is there a way for me to get the Pivot Table to just display the values instead of aggregating/summarizing them?

Also, if anyone knows of a different way that would allow me to organize the data set using "User IDs" as rows and "Questions" as columns that does not use Pivot Tables, that would be fine too.

1
excelpivot-tableworksheet-function

1 Answers

0
votes

Basically you need to create 2 lists of DISTINCT values, a vertical list of unique User IDs, and a horizontal list of unique Questions. There are various ways to do that with an array formula - look here for example.

Fortunately, since you don't mind using a pivot table, you can also "cheat" and just let the pivot fill the content with aggregated nonsense, then just grab the headers with a simple reference formula (=A1, =A2 and so on).

Ultimately you goal is to achieve:

                First question... | Second question... | And another question... | ...
621546
621547
621548
...

Then you can use an IF statement in each "content" cell to grab the correct result:

{=OFFSET(Source!$C$1,MAX(IF(($A1=Source!$A$1:$A$100)*(B$2=Source!$B$1:$B$100),ROW($1:$100)))-1,0)}

That's an array formula, so make sure you Ctrl+Shift+Enter. What it does is conditionally (on matching both user and question) returns the row number of the match, aggregates the returned values (1 number and a lot of false values) into the single row number value, and then use that to access the response itself.