Lookup letters and display highest values in a pivot table

[Guest]

EMPNAME Type c1 c2 c3
Name SE Y Y N
Name r4 O Y O
Name r3 Y Y O
Name2 SW Dev N N Y
Name2 SE Y Y N
Name3 r3 Y Y O

Pivot table result should be

Data
EMPNAME Max of c1 Max of c2 Max of c3
Name Y Y N
Name2 Y Y Y
Name3 Y Y O


Where Y = 3, N = 2, O=1

 

[Debra Dalgleish]

In the source data, add columns to calculate the number for each letter.
For example, in cell F2:
=VLOOKUP(C2,NumLU,2,0)
where NumLU is a table of the letters and their number values

Then, add the new columns to the pivot table, instead of using the
original c1, c2, c3 columns.

Format the fields in the pivot table with custom number formats:
Right-click on a data field heading, and choose Field Settings
Click the Numbers button
From the Category list, choose Custom
In the text box, enter:
[=1]"O";[=2]"N";"Y"
Click OK, click OK

 

[Guest]

Thanks this works! How do I update the headings to just show c1 c2 c3 instead
of Max of c1, etc.?

In the source data, add columns to calculate the number for each letter.
For example, in cell F2:
=VLOOKUP(C2,NumLU,2,0)
where NumLU is a table of the letters and their number values

Then, add the new columns to the pivot table, instead of using the
original c1, c2, c3 columns.

Format the fields in the pivot table with custom number formats:
Right-click on a data field heading, and choose Field Settings
Click the Numbers button
From the Category list, choose Custom
In the text box, enter:
[=1]"O";[=2]"N";"Y"
Click OK, click OK

EMPNAME Type c1 c2 c3
Name SE Y Y N
Name r4 O Y O
Name r3 Y Y O
Name2 SW Dev N N Y
Name2 SE Y Y N
Name3 r3 Y Y O

Pivot table result should be

Data
EMPNAME Max of c1 Max of c2 Max of c3
Name Y Y N
Name2 Y Y Y
Name3 Y Y O


Where Y = 3, N = 2, O=1

 

[Debra Dalgleish]

You're welcome.
To change the heading, select the cell, and type c1 followed by a space
character: c1<space>
You can't use the exact field name from the source table, but if you add
the space character it will look the same, and Excel will accept it.

Thanks this works! How do I update the headings to just show c1 c2 c3 instead
of Max of c1, etc.?

:

In the source data, add columns to calculate the number for each letter.
For example, in cell F2:
=VLOOKUP(C2,NumLU,2,0)
where NumLU is a table of the letters and their number values

Then, add the new columns to the pivot table, instead of using the
original c1, c2, c3 columns.

Format the fields in the pivot table with custom number formats:
Right-click on a data field heading, and choose Field Settings
Click the Numbers button
From the Category list, choose Custom
In the text box, enter:
[=1]"O";[=2]"N";"Y"
Click OK, click OK

EMPNAME Type c1 c2 c3
Name SE Y Y N
Name r4 O Y O
Name r3 Y Y O
Name2 SW Dev N N Y
Name2 SE Y Y N
Name3 r3 Y Y O

Pivot table result should be

Data
EMPNAME Max of c1 Max of c2 Max of c3
Name Y Y N
Name2 Y Y Y
Name3 Y Y O


Where Y = 3, N = 2, O=1