3

I am keying some data and one question from the data set supports multiple answers. As best I can tell, I have the choice of keying this as a delimited column, e.g.

Answer1
------
A
A,B
C
B,D

Or, I can key it as multiple columns:

Answer1      Answer1
-------      -------
A     
A            B
C
B            D

Either way, I need to be able to summarize this data using a pivot table, but I'm not sure how to go about this? Suggestions?

The resulting pivot table should look something like:

Answer1     Count
-------     -----
A           2
B           2
C           1
D           1
Improve this question
2
  • What do you want to show in your pivot table?
    – CallumDA
    Jan 22, 2015 at 17:00
  • Edited to add pivot table example
    – Chris
    Jan 22, 2015 at 17:05

2 Answers 2

Reset to default
2

Add calculated columns where $A2 is the column containing the comma-separated list and "A" is whichever answer you want to count in the calculated column.

=IFERROR(FIND("A",$A2)^0,0)

This assumes that a given answer can only exist once in A2. If it finds it, the location is raised to the power of zero, which gives you 1. If it can't find it, you get an error which results in IFERROR() producing 0.

Answer1  1A  1B  1C  1D
A         1   0   0   0
A,B       1   1   0   0
C         0   0   1   0
B,D       0   1   0   1

You can now get a sum of each answer either with SUM() or by using a PivotTable.

Improve this answer
0

In which case you need to rearrange your data to look like this:

Answer 1
  B
  A
  A
  C
  D
  D

Then select the data (inc. heading) and put "answer 1" field into Row and Values fields.

UPDATE

As an example, once ethe data is in this format, you can bring it together however you like - such as below:

enter image description here

Improve this answer
3
  • Cannot simply "rearrange" the data. It's part of a larger set. Think of a mail-in survey. Columns could be FirstName,LastName,...,Answer1,Answer2, etc. In your case, I'd have duplicate rows for each person depending on the number of answers.
    – Chris
    Jan 22, 2015 at 17:22
  • Excel loves rows, which is why it gives you loads more rows that columns. If you want to use a pivot table in the way you've described you will need to get your data into that format - duplicating rows where people have selected two options. The pivot table will give you flexibility to bring the "duplicated" data back into one place if you want.
    – CallumDA
    Jan 22, 2015 at 17:32
  • I've updated my answer to add illustration to this point.
    – CallumDA
    Jan 22, 2015 at 17:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .