I have a excel spreadsheet with exam results in.  I need to pivot the table to get the results in a different format.  The pivot table will not keep the exam grade but inserts a number instead.  Is there a way to get it to keep the text?  I have found a solution online but you can only have 3 different text values.

This is a simplified example of what I have ..

Pupil      Subject    Grade

Boy 1       Maths       A 

Boy 1       Physics     B

Boy 1      Chemistry  C

Boy 2      Maths        AB

Boy 2      Physics      B

Boy2       Chemistry  D

and this is what I need....

Pupil       Maths      Physics     Chemistry

Boy 1        A              B                C

Boy 2       AB             B                D

Any suggestions gratefully received.

and what I need:

  I may be being dense, please humour if I am!

A pivottable can't do this - the value field in a pivottable MUST use one of the aggregation functions SUM, COUNT, MIN, AVERAGE etc., so it is always a number.

If you create a table with the pupils' names in the first column and the subjects in the top row, you can use formulas to retrieve the grades.

Let's say that your table is in A1:C51 (with headers in A1:C1), and that you enter the pupils' names in E2, E3, ... and the subjects in F1, G1, ...

In F2, enter the following array formula, confirmed with Ctrl+Shift+Enter, not just Enter:

=INDEX($C$2:$C$51,MATCH($E2&F$1,$A$2:$A$51&$B$2:$B$51,0))

Fill this formula down to the last pupil, then right to the last subject.

Excel 2010 Table, PivotTable
Show text in Pseudo PivotTable
With macro.
http://8936e6d9f85be098ae08-f26298a42cfbd9c5a8f09838ce8d1b1e.r90.cf1.rackcdn.com/10_13_12.xlsm

If you get *.zip, don't unzip, just rename *.xlsm

Perfect.  Thank you.

My macro is stopping at this point - I have have named my data "Table2" in the "Data" Sheet

With Sheets("Data").ListObjects("Table2")

It's not clear to me how your question relates to the rest of this old thread.

Good afternoon Hans,

The above has helped me a lot as I have been trying to do the same without realising that Pivot tables will only dispaly numeric values. My question, I am getting a #VALUE! error message. My col E2 is Alpha numeric value ie C1234567, my  F1 is also alpha numeric ie A120, A125, SAVO and my values are all alpha, including spaces.

What format must I save my data in General, text or numeric?

Instead of Ctrl+Shift+enter I have copied the formula and pasted it into the formala bar for E2 - is this the same?

I hope you can help me. A collegue did the below formalua for me based off your formula. I am trying to do basically the same as the above example.

=IF(ISNA(INDEX($G$3:$G$100,MATCH($Q3&R$2,$B$3:$B$100&$C$3:$C$100,0)))," ",INDEX($G$3:$G$100,MATCH($Q3&R$2,$B$3:$B$100&$C$3:$C$100,0))) 

After pasting the formula into the formula bar, you must confirm it with Ctrl+Shift+Enter, otherwise it won't work correctly.