SORTING TEXT ALPHABETICALLY IN EXCEL USING FORMULA
Sorting data is the basic part of data analysis. You might want to know that ‘how to dynamically sort text’? Simple is to use excel COUNTIF function to sort text in alphabetical order.
=COUNTIF(sort range,"<=" & current Cell)
The COUNTIF formula can be used to sort text values alphabetically. In the example, the formula in C5 is:
Figure 1. SORTED TEXT AFTER BASIC TEXT SORT FORMULA IN C5
“States” indicates the named range B4:B13
What is named range?
A range of one or more cells defined by a specific name is called a named range.
HOW TO USE THIS SORT TEXT FORMULA?
In this formula, you can use “>=” (greater than or equal to) operator with the text. It is something amazing because, during the text comparison, Excel decides the “greater” values than others following the ASCII numbering scheme.
In the example, the range is defined as the named range “states“, whereas, the criteria are “<= (less than or equal) to the value in C5, inside COUNTIF. It returns the number of values that are less than or equal to the current value. As a result, we get a sequential list of numbers in C5:C13 (helper Column).
HOW TO LIST SORTED VALUES?
We can use the helper column to retrieve the sorted values by their position. In this example, the formula used in E5 to retrieve values is
=INDEX (states,MATCH (ROWS ($E$5:E5), helper,0 ))
This INDEX and MATCH formula uses an expanding reference, the expanded formula that can be copied across the cells, to generate consecutive numbers fed into MATCH. The MATCH function finds the location of each number while the INDEX retrieves the value at its position.
HOW TO HANDLE DUPLICATES?
If there is duplication in the text values, when you use the INDEX function to retrieve the values, same will occur in the sequence of sort numbers. To solve this problem, second COUNTIF can be used with the variation of the formula to increment duplicates.
=COUNTIF(states,"<" & B5) + COUNTIF ($B$5:B5, B5)
Note the logical operation with adjusted first COUNTIF function while the second COUNTIF function’s range is an expanding reference.