Is it possible to get text values in a pivot table?

M

Marty L

From this:

ROW COL VAL
A HD1 X
B HD2 Y
B HD1 Z
C HD3 J
A HD3 Z
B HD3 R
C HD1 S



Get this?

Var of VAL COL
ROW HD1 HD2 HD3
A X Z
B Z Y R
C S J



(If 2 rows go to same cell, would need to get maximum or first).
 
B

Bernard Liengme

Not with Pivot Table
Let you A, B C data be A1:A7, the HDn data in B1:B7 and the other data in
C1:C7
In some convenient place (I use A11) enter =A1&B1 and copy down the column
(you could put this in another sheet or in a hidden column)

Make the heading HD1, HD2 - I did this in F1:H1 and the row headings A, B, C
in E2:E4
In F2 (the intersection if the column heading HD1 and row heading A) enter
=IF(ISERROR(MATCH($E2&F$1,$A$11:$A$17,0)),"",INDEX($C$1:$C$7,MATCH($E2&F$1,$A$11:$A$17,0)))
Copy this across 2 more columns and then down 2 more rows to get the
required result.

best wishes
 
H

Herbert Seidenberg

Here is another way with Pivot Table,
but it can't be easily refreshed.
Assume your data is arranged like this,
(I added a duplicate last entry to exercise your MAX option)

Alpha Hd LData DCode
A HD1 X 88
B HD2 Y 89
B HD1 Z 90
C HD3 J 74
A HD3 Z 90
B HD3 R 82
C HD1 S 83
C HD1 T 84

Add the column DCode with the formula:
=CODE(LData)
Make Alpha your ROW, Hd your COLUMN
and Min of DCode your DATA
The Pivot Table will look like this:

Alpha HD1 HD2 HD3
A 88 90
B 90 89 82
C 83 74

Name the 3x3 array of numbers ArrayA and create
a same sized array named ArrayB with this array (CSE) formula:
=IF(ISNUMBER(arrayA),CHAR(arrayA),"")
ArrayB will look like this:

X Z
Z Y R
S J

Select the entire Pivot Table and ArrayB and
Copy > Paste Special > Values
and drag or copy ArrayB into ArrayA.
 
Top