Excel Cross Referencing

Question

Got an interesting problem that seemed trivial at first...

I have a table in the following format:

USER | QUESTION | ANSWER 
========================
1    | 1        | A
1    | 2        | B
1    | 3        | D
2    | 1        | A
2    | 2        | B
2    | 3        | A
3    | 1        | B
3    | 2        | A
3    | 3        | D

I am trying to generate a pivot table that looks something like:

             | QUESTION (2)  
-------------+---+---+---+---
QUESTION (1) | A | B | C | D 
-------------+---+---+---+---
           A | 0 | 2 | 0 | 0
           B | 1 | 0 | 0 | 0
           C | 0 | 0 | 0 | 0
           D | 0 | 0 | 0 | 0

That is, semantically, "people who answered question A with answer B and also answered question X with answer Y", for each possible answer.

Answer 1

The issue is that the way you've got your data set up can't be laid out that way; because all the answers come under one column.

When designing raw data each row should represent a discrete element, in this case that element is a single instance of a test or survey being taken. So all data about that instance should exist in one row.

Amend your data so that you have a single row for each user and a column for each question.

USER | Q1 | Q2 
==============
1    | A  | A
2    | D  | B
3    | C  | D

Next build your pivot table and put Q1 in the rows and Q2 in the columns, with a count of user in the Values.

The only issue is that if an answer doesn't show up for a question in the source data there isn't an entry for it in the pivot table, but that pretty clearly means no-one answered with that combination.

An alternative
You want to build a matrix, so the answer above shows you how to do that, but you have more than two questions and a matrix only works for two series. Instead try this:

1. Make your table as above, with all the question columns you want
2. Make your pivot table, but this time drop all the questions into the Rows field
3. In the PIVOTTABLE TOOLS tab, DESIGN, press Report Layout and select Tabular
4. Open that dropdown again and select Repeat All Item Labels

Now you'll have a discrete table of all actual combinations and the counts of how often those combinations came up.