Pivot Table Case Sensitivity

Hi everyone,

I've got a problem that I've been googling for a while now. I've got a data set in excel like so:

ID ______| Name
-----------------------
abc123A | John Doe
AbC123a | Spiderman
aBc123A | The Maxx
123ABC1 | Zues
123abC1 | Dartanian

If I create a pivot table with ID in the row area and Name in the data area, it gives me a count on each unique occurrence of ID.

My problem lies in the fact that it doesn't consider a letter's case to be an element of uniqueness. The pivot table looks like this:


ID ______| Name
-----------------------
abc123A | 3
123ABC1 | 2

What I need is a real unique count that can also be clicked on to view the results like you can with a pivot table. Any ideas?

Originally Posted by martindwilson

since pivot tables dont do case
perhaps using the ascii code instead for the string may work
this assumes your codes are all the same length (7 characters)
=CONCATENATE(B2,C2,D2,E2,F2,G2,H2) is in j2 and draged down
adjust as necessary(problem wil occur if they are not the same length as you can't concat. #value)
see attached

Thank you, your idea worked wonderfully. The only issue I had with it was that it was a little difficult to pass on to the user as a standard way of doing things. What I ended up doing was creating some vbscript to convert the cells. It's not perfect, and it's pretty rough with some unnecessary stuff but it works.

Please Login or Register  to view this content.

Hopefully this will prove helpful to someone with uppercase or lowercase uniqueness issues with pivot tables.

Thank you,

khoury