Jun 30 2021 05:20 PM
I have a shared Excel-based database with some colleagues. One of the database attributes is the type of Schedule, which can be one of the A, a, C, c, G, g, S, s, etc. letters. When I attempt to sort or filter the database based on Schedule, the small and capital letters (i.e., A and a) are treated the same and would not be sorted correctly. I changed the database to an Excel official table, but that was not a successful move either. I am seeking opinions on correctly sorting and filtering the database, without adding any extra column. An extracted section of the database is attached.
Jun 30 2021 08:17 PM
SolutionHi, @SaeedShaeri
here are two possible options of doing this without an extra column. I am using Excel for Microsoft 365.
The first option puts small case before the capital letters when sorting. See if this works for you:
Click anywhere on the table > File Tab > button Sort & Filter > Custom Sort > button Options > checkmark on "case sensitive" > OK.
[see pic 1 attached]
The other option is to use a Custom List.
First create the custom list by going to File > Options > Advanced
scroll down to General
click button "Edit Custom Lists..." > click where it says NEW LIST and add your list sorted the way you want.
Then go to Custom Sort again and under "Order" choose "Custom List":
[see pic 2 attached]
Jul 01 2021 05:03 PM
Jul 01 2021 09:20 PM
Jul 02 2021 08:17 AM
Jul 03 2021 03:55 AM
Jul 03 2021 05:10 AM
To solve the issue as non VBA method, you need a Helper Data Column.
How it works:
=IF(EXACT(AQ2,UPPER(AQ2)),"UC",IF(EXACT(AQ2,LOWER(AQ2)),"LC",IF(EXACT(AQ2,PROPER(AQ2)),"PC","otr")))
N.B.
You find all small letters/alphabets first then Proper case data and finally capital letters/alphabets.
N.B.
Jul 03 2021 01:34 PM
Jul 03 2021 10:31 PM
Jul 05 2021 12:28 PM
if you end up using custom lists, since you mentioned multiple users, I believe the list must be added to each machine where the file is used. I think I forgot to mention this detail.
Aug 27 2021 09:23 AM
Just passing by to let you know that I published a video with my answer to this question.
I thought the question was interesting and I hope it can help other people as well. :)
Jun 30 2021 08:17 PM
SolutionHi, @SaeedShaeri
here are two possible options of doing this without an extra column. I am using Excel for Microsoft 365.
The first option puts small case before the capital letters when sorting. See if this works for you:
Click anywhere on the table > File Tab > button Sort & Filter > Custom Sort > button Options > checkmark on "case sensitive" > OK.
[see pic 1 attached]
The other option is to use a Custom List.
First create the custom list by going to File > Options > Advanced
scroll down to General
click button "Edit Custom Lists..." > click where it says NEW LIST and add your list sorted the way you want.
Then go to Custom Sort again and under "Order" choose "Custom List":
[see pic 2 attached]