Case Sensitive Pivot Table

[wolfratt49]

I'm in the process of creating a database of contacts for my department, and I like the functionality of pivot tables, but there is one big issue. when the usernames and passwords were set up for the various accounts involved, they were made somewhat haphazardly. Many of the usernames and passwords vary only in Case, but because of the way pivot tables look at case (or rather, don't look at case) it overrides the case in the database with one higher up the chain. for example, company a has a password of Aaaaab, but company b's password is aaaaab. When this flows through to the pivot table, company b's password is overwritten with that of company a.

company	password
A	Aaaaab
B	aaaaab

becomes

company	password
A	Aaaaab
B	Aaaaab

Is there anything that can be done to correct this issue, or am I SOL?

Worth noting is that, while i'm experienced with excel on a function level, I've done very little with VBA and macros. I'm not completely clueless, but this issue is way out of my league.

[sandy666]

so maybe try PowerQuery - it's case sensitive

[CK76]

It really depends on what patterns exist in your password column.

One way to avoid case insensitivity issue is to add helper column to source data.
Where you add singe space character when certain condition(s) are met.

In your example something like...
=IF(EXACT(LEFT([@Password],1),UPPER(LEFT([@Password],1))),[@Password],[@Password]&" ")

See attached.

FYI - PowerQuery is case sensitive, but not when it's loaded to Pivot... It's annoying quirk in pivot/data model.

[sandy666]

If you do good work with PowerQuery you don't need PivotTable

[CK76]

If you want to look at different dimensions and quickly summarize data using measures. Pivot is a must

But yes, for static summary PowerQuery does well enough.

[sandy666]

We both don't really know what OP tryin' to achieve, so I will stay with my opinion (but I am open for your way too )

[CK76]

Oh, if there are more than simple first character upper vs. lower pattern...

You can create helper column that concatenates company with Password (as this should produce unique result and context).

[sandy666]

Right, but for now I see text only (post#1) so I'll wait for more info.

[wolfratt49]

Part of the issue arises from the randomness of the case differences. I think I may have to do the space thing, which sucks because i'm trying to make this thing idiot resistant so people can copy/paste passwords. But I will if I have to. Looking into power query, is it an add on for excel, or a stand alone program?

[sandy666]

PQ is add-in for Ex2010, 2013 or built-in for Ex2016, 365 (Pro Plus for all)

[wolfratt49]

I'll have to persuade my boss to let me try it. Thanks for your help, i'll let you know if I get it sorted

[sandy666]

You are welcome