0

I have a simple dataset as follow:

Name    Question    Answer
A       Allergy     Y
A       Cancer      N
A       Flu         N
B       Allergy     Y
B       Cancer      Y
B       Flu         N
C       Allergy     N
C       Flu         N

I would like to display it in the form (in excel)

      Allergy   Cancer Flu
A     Y            N   N
B     Y            Y   N
C     N                N

And I wonder if using pivot table is the way to go. If not, is there a way to display data using my format above? Please note, the dataset I presented above is just a simplified version of a database with million records.

I tried to use Pivot Table in excel with Name in the Row, Question in the Column and Answer in the detail with calculation function Max. The results did not turn out as expected.

enter image description here

Please advise how I should tweak the results to get what I want. I do not need any calculation performed, just show the result as is. Possible?

user1205746
  • 3,110
  • 11
  • 44
  • 73
  • Since `y` and `n` are "Boolean" they are functionally equivalent to a `1` and `0`. Could you switch them over and pivot them like that? – JNevill Dec 06 '17 at 20:35
  • **Have you tried Googling Pivot Table tutorials**? Do you have a specific coding question? – ashleedawg Dec 06 '17 at 20:35
  • @JNevill: Good points. However, some answers are not binary. I simplified the answer. – user1205746 Dec 06 '17 at 20:40
  • That makes sense then. I believe this is a possible duplicate of [Pivot in Excel without aggregation, to show text, not numbers?](https://stackoverflow.com/questions/32767117/pivot-in-excel-without-aggregation-to-show-text-not-numbers). Unfortunately there is no great answer here without that power pivot thing possibly. Personally would cook up some VBA to handle this, but it could get ugly if you are not comfortable with VBA. – JNevill Dec 06 '17 at 20:54
  • @JNevill: Thank you for pointing out similar issue. So the answer is making it a powerpivot. That does help a lot.. Very much appreciate your effort to assist people new with excel than somebody just merely criticising it without any constructive suggestion. – user1205746 Dec 06 '17 at 21:01
  • Power pivot won't make a difference. Any pivot only has numbers in the value area. Even Power Pivots. – teylyn Dec 06 '17 at 21:03
  • @teylyn I misspoke. It's the MS Power Query Add-in. I don't use either, so I get them confused. At any rate, hit up that suggested duplicate question link and hopefully one of the suggestions there will do the trick. – JNevill Dec 06 '17 at 21:05
  • @user1205746 No worries. It's easy to get jaded on here. Sometimes the questions are abusively bad. "How do sum up two cells and put that sum in a third cell" type stuff. Other times people copy and paste their very obvious homework question in and hit submit. – JNevill Dec 06 '17 at 21:07
  • Thank you both teylin and JNevill. Will read the response from the other thread.. the answer is kinda confusing... will see how it would apply to my scenario. – user1205746 Dec 06 '17 at 21:08
  • I'm not talking about either of them. I am simply pointing to a link in previous comment where this exact same problem was solved. If you have a problem with the use of power query to answer this question then go tell the person that submitted that as answer. Click the link, scroll down, and type there. – JNevill Dec 06 '17 at 21:11
  • PowerQuery is indeed the best way to tackle this particular nut. But at the link referenced above you can also see a VBA approach I've used before to do this. You can overwrite the numbers that a PivotTable serves up in the values with text. This means you can then use a mapping table to thus turn numbers into some textual representation. But it is a pretty convoluted workaround. – jeffreyweir Dec 07 '17 at 03:56
  • @jeffreyweir: Yes, the VBA is a pretty convoluted and interested solution. I am surprised Microsoft did not help make the solution better. They could just easily add a function to list the value of the cell beside the standard functions such as max, min, count, etc.. totally doable in my opinion but they made us jumping the hoops to get the solution. – user1205746 Dec 07 '17 at 04:03

2 Answers2

0

by using a pivot table it is nessesary to have a numeric field to place in values, otherwise it will count the number of occurences of every class.

So I think a good solution would be to creatre a binary variable definied as follows:= 1 if Yes 0 otherwise

then you would have something like this (A contingency table):

enter image description here

Moreno
  • 608
  • 1
  • 9
  • 24
  • Thank you for the response. However, some answers in the real database are not binary that can represented by a yes or no. Like allergy what kind of allergy... – user1205746 Dec 06 '17 at 20:44
  • If you could share a brief sample of you real data i would be nice – Moreno Dec 06 '17 at 20:56
0

A pivot table needs numbers in the values area, so it is not the solution in this scenario.

If the source data table is sorted ascending by name, the result can be achieved with a formula. You can use a pivot table to create a list of unique names for the results table that starts in column E in the screenshot. Enter the column labels manually, then use the following formula in F3, copy down and across.

=VLOOKUP(G$2,INDEX($B:$B,MATCH($F3,$A:$A,0)):INDEX($C:$C,MATCH($F3,$A:$A,1)),2,FALSE)

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73