Pivot Table

[carl]

I have a table like this:

Type Number
AA 1
aa 2
Aa 3
aA 4


When I use the Pivot wizard i get this result:

Sum of Number
Type Total
AA 10
Grand Total 10


Is there a setting that will enable the Pivot to recognize the upper and
lower case types as unique ?

Thank you in advance.

 

[Bernie Deitrick]

Carl,

A comment first - using exact strings with the exception of case is a prescription for data
corruption. BAD, BAD idea. Far better to have completely unique values for the type values.

That said, if you need to work with existing data, either do a replace (check the match case option)
and insert new values for the type, or use a formula like this in a new column

=IF(EXACT(B2,"AA"),1,IF(EXACT(B2,"aa"),2,IF(EXACT(B2,"Aa"),3,IF(EXACT(B2,"aA"),4,"NA"))))

to categorize your types, then use that category as the first of your row fields, with type as the
second. Excel will group the 1s, 2s etc if you do it that way.

HTH,
Bernie
MS Excel MVP