Pivot table to list multiple source columns in rows

Hello all,

could somebody help me with this problem:

I have a huge data set with survey data (sample attached: sample.xlsx).
Column headings: survey questions
Row headings: respondent identifiers (names)
Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)

I need to build that kind of pivot table:

Rows - list of particular questions (i.e. questions no. 3, 7 and 12)
Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference)
Values - count answer entries (i.e. how many answers "5" are on quesiton 3)

------
UPDATE: explanation added.

The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...

i.e. how people who answered 1,2 or 3 to question 12, answered to quesitions 2, 8, 9 and 102. if I manage to get these pivot tables, i'll be able to do such analysis by adding filters.
-------


Thanks a lot in advance!

G.

Hi there. It's SUMPRODUCT that you need.

Try this out...

With this macro to re-arange the data.

After that you can easy analyze with the desired pivot table.

Please Login or Register  to view this content.

If that is your desired result, you could record the macro with the macro-recorder.

I have no macro for that available.


Advice:

You noticed the sort of the question is not logic.

You could change the question 1 to 01, 2 to 02 etc.

In that case the data will sort on a logic way.