1
I have a CSV file I opened with Excel 2013 because I wanted to find errors in a specific column.
Here is an extract of data:
"ID";"LANGUAGE";"TYPE";"NAME";"ACTIVE";"CITY";"POSTAL_CODE";"COUNTRY_CODE"
"1";"EN";"B";"Beirut";"O";"Beirut";70018;"LB"
"1";"DE";"B";"Beirut";"O";"Beirut";70018;"LB"
"2";"EN";"B";"NYC 1";"O";"New York";"NY 10010";"US"
"2";"DE";"B";"NYC 1";"O";"New York";"NY 10010";"US"
"3";"EN";"B";"Buenos Aires";"O";"Buenos Aires";"C1129AAN";"AR"
"3";"DE";"B";"Buenos Aires";"O";"Buenos Aires";"C1129AAN";"AR"
"17";"EN";"B";"Geneva";"o";"Geneva";1201;"CH"
"17";"DE";"B";"Geneva";"O";"Geneva";1201;"CH"
"18";"EN";"B";"Hong Kong Landmark";"O";"Hong Kong";" ";"HK"
"18";"DE";"B";"Hong Kong Landmark";"O";"Hong Kong";" ";"HK"
"19";"EN";"B";"Hong Kong Canton";"O";"Hong Kong";" ";"HK"
This is just a sample because in reality I have more than 5'000 line and more columns as well.
The thing is I have a mistake in the column "ACTIVE" where I can only have values 'O' or 'C' and if I import this in Excel, and use an auto-filter, the list of all values for that columns is only 'O' which seems correct like shown in the following picture:
However, at line with ID='17' & language='EN', the code is 'o' and not 'O' which should be considered as a mistake for my use case.
When I try the same in LibreOffice I have the possibility to change the filter to be case sensitive as the next picture shows:
And then the auto-filter works in LibreOffice:
I am wondering if the same option exist in Excel 2013 and how to do it?
рüффп
Posted 2016-12-20T21:17:39.047
Reputation: 575
Would you be adverse to using a helper column? – Scott Craner – 2016-12-20T21:23:05.887
@ScottCraner if this is the only way, why not. However I would prefer to have a separate Excel which can contains Macros (VBA) or helper column to validate an external CSV file. Anyway personally I will continue with LibreOffice as long people are still sending me wrong files. I just wanted to know if that option was easily available in Excel as well. – рüффп – 2016-12-20T21:36:04.670
Unfortunately it is not an option, Excel's filter in not case sensitive, and there is no way to supersede the default. – Scott Craner – 2016-12-20T21:39:08.713