Pivot Table

C

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.
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If Function 1
Min Max Question 4
Summarizing Data 1
Find next number 2
Sumproduct or ????? 5
Index / Match Question 1
INDEX OR Match Problem 1
SUMPRODUCT 5

Top