SOLVED

Sorting small and capital letters correcly

Copper Contributor

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.

10 Replies
best response confirmed by SaeedShaeri (Copper Contributor)
Solution

Hi, @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]

 

Thank you @Celia_Alves. These sound simple now.
If you want to SORT in natural order like Lower Case then Upper like,,, a A b B then use built in option Case Sensitive with Custom Sort as suggested by @Celia_Alves , otherwise alphabets can be Sorted like all LOWER CASE letters first then UPPER CASE,,, confirm through reply then I'll show you how !
Great! If my suggestion helped, please consider to click on "Mark as best response" under my answer. Thank you and good luck!
@Rajesh_Sinha, that is an interesting idea. I am happy to explore this option as well. Please share your suggestion.

@SaeedShaeri 

 

To solve the issue as non VBA method, you need a Helper Data Column.

 

RajeshS_0-1625312686436.png

 

How it works:

  • Formula in cell AP2:
=IF(EXACT(AQ2,UPPER(AQ2)),"UC",IF(EXACT(AQ2,LOWER(AQ2)),"LC",IF(EXACT(AQ2,PROPER(AQ2)),"PC","otr")))

N.B.

  • Values UC, LC, PC & otr are adjustable, it may be 0 for digits & 1 for Special characters,  2 for Lower case, 3 for Proper, 4 for Upper case.

 

  • Select AO1:AP10, then go to Custom Sort.
  • Set Help Data as First Level & Unsorted as Second Level.

RajeshS_1-1625313881030.png

 

  • Finish with Ok.

You find all small letters/alphabets first then Proper case data and finally capital letters/alphabets.

 

N.B.

  • For neatness you may hide Help Data column.
  • To follow the hierarchy of data while SORT you may extend your formula to check DIGITS, SPACES & SPECIAL Characters.

 

Thanks @Rajesh_Sinha. I'd follow your approach if I were the sole user. With a shared database, I prefer not to add extra columns.
Using extra column is not a big issue if your data set is shareable even,,, Copy Sorted data and Paste as Link,,, other create Helper column at far from main data set columns,,, Another is VBA macro,,, without extra column,, are you comfortable with that ?

@SaeedShaeri 

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.

@SaeedShaeri 

Just passing by to let you know that I published a video with my answer to this question.

https://youtu.be/cCGn2QtKWJA

 

I thought the question was interesting and I hope it can help other people as well. :)

1 best response

Accepted Solutions
best response confirmed by SaeedShaeri (Copper Contributor)
Solution

Hi, @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]

 

View solution in original post