Pivot Table Case Sensitivity

**khoury** · 08-08-2008, 07:53 PM

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?

**martindwilson** · 08-09-2008, 03:52 PM

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
formula in b2 is an array formula confirm with ctrl+shift+enter
drag across to length of your code string then down to end of list
you should then be able to construct your pivot table from yellow highlighted
area
edit:
if codes are of varying length try instead

=IF(ISERROR(CODE(MID($A2,COLUMN(A1:OFFSET(A1,LEN($A$2)-1,0)),1))),"",CODE(MID($A2,COLUMN(A1:OFFSET(A1,LEN($A$2)-1,0)),1)))

in B2 it's still an array formula but will not show #value and you can concat. to your hearts content

**khoury** · 08-12-2008, 01:46 PM

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.

Private Sub Ascii_For_Pivot()
    Dim i As Long
    i = 2 ' Start at Row 2 to skip the column header

    For Each Cell In Range([A2], [A2].End(xlDown))
        
            stringcode = Cells(i, 1).Value
                Cells(i, 2) = "1"
            For charnum = 1 To Len(stringcode)
                Cells(i, 2) = Cells(i, 2) & Format(Asc(Mid$(stringcode, charnum, 1)))
            Next charnum
        i = i + 1
    Next

End Sub

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

Thank you,

khoury