Forum Discussion
Solved
data filter and pivot table manipulate data
Given
A list of 12 street names in different notations (column ORIGNALSCHREIBWEISE)
Problem I - Filter
Adding a filter and opening the dropdown list shows a reduced number (9 of 12) of ORIGNALSCHREIBWEISE-entries with standardised notations:
Problem II - pivot table
Creating a pivot table from the list the result is the same as in the filter drop down list: the number of notations is reduce to 9:
Error Interpretation
In both test cases Excel doesn't work case sensitiv. It interprets
- Sankt-Martin-str. / Sankt-Martin-Str.
- St.-martin-Str. / St.-Martin-Str.
- St.-Martin-straße / St.-Martin-Straße
as identical strings.
Question
How can I force Excel to work in both cases case sensitive?
With the following formula, you can use a workaround. As far as I know, you cannot make pivot tables case sensitive.
Each capital letter is marked with a 0-wide space (unichar 8203).
=TEXTJOIN("",TRUE,IF(UNICODE(MID(B2,SEQUENCE(LEN(B2)),1))>64,IF(UNICODE(MID(B2,SEQUENCE(LEN(B2)),1))<91,UNICHAR(8203),""),"")&MID(B2,SEQUENCE(LEN(B2)),1))So you don't see that at all. Pivot will still differentiate according to whether it is present.
Ich hoffe das hilft dir weiter.
3 Replies
With the following formula, you can use a workaround. As far as I know, you cannot make pivot tables case sensitive.
Each capital letter is marked with a 0-wide space (unichar 8203).
=TEXTJOIN("",TRUE,IF(UNICODE(MID(B2,SEQUENCE(LEN(B2)),1))>64,IF(UNICODE(MID(B2,SEQUENCE(LEN(B2)),1))<91,UNICHAR(8203),""),"")&MID(B2,SEQUENCE(LEN(B2)),1))So you don't see that at all. Pivot will still differentiate according to whether it is present.
Ich hoffe das hilft dir weiter.
Thank you for your interesting feedback.
Your solution is not the first thing that comes to mind, but it is an elegant solution to my problem.
It would certainly be better if there was a switch in Excel or Pivot for case sensitivity. That is my request for improvement to Microsoft.
Thanks again for your efforts!
