Sort Data in Alphabetical Order
In this post, I will show you various ways to sort data in alphabetical order using formulas. This means you can add data, and it will automatically sort it for you.
When the Data is all Text with No Duplicates
Suppose you have a data as shown below: In this example, all the data is in text format (no numbers, blanks or duplicates). To sort this, I will use a helper column. In the column next to the data, use the following COUNTIF formula:
This formula compares a text value with all the other text values and returns its relative rank. For example, in cell B2, it returns 8, as there are 8 text values that are lower than or equal to the text ‘US’ (alphabetical order). Now to sort the values, use the following combination of INDEX, MATCH and ROWS functions: This formula simply extracts the names in the alphabetical order. In the first cell (C2), it looks for the country name that has the lowest number (Australia has 1). In the second cell, it returns Canada (which has the number 2) and so on.. Allergic to Helper Columns?? Here is a formula that will do the same without the helper column. This is an array formula, so use Control + Shift + Enter instead of Enter. I will leave it for you to de-code. Try it Yourself.. Download Example File This formula works well if you have text or alphanumeric values. But it fails miserably if:
You have duplicates in the data (try putting US twice). There are blanks in the data. You have a mix of numbers and text (try putting 123 in one of the cells).
When Data is a Mix of Numbers, Text, Duplicates, & Blanks
Now this one is a bit tricky. I will use 4 helper columns to show you how it works (and then give you a huge formula that will do it without the helper columns). Suppose you have a data as shown below: You can see there are duplicate values, blank and numbers. So I will use helper columns to address each of these issues. Helper Column 1 Enter the following COUNTIF formula in Helper Column 1
This formula does the following:
It returns 0 for blanks. In the case of duplicates, it returns the same number. Text and numbers are processed parallelly and this formula returns the same number for text and number (for example 123 and India both get 1).
Helper Column 2 Enter the following IS Function in Helper Column 2:
Helper Column 3 Enter the following formula in Helper Column 3:
Helper Column 4 Enter the following formula in Helper Column 4
The idea for this formula is to segregate blanks, numbers and text values.
If the cell is blank, it returns the value in cell B2 (which would always be 0) and adds the value in cell D10. In a nutshell, it will return the total number of blank cells in the data If the cell is a numerical value, it will return the comparative rank and add the total number of blanks. For example, for 123 it returns 2 (1 is the rank of 123 in the data, and there is 1 blank cell) If it is text, it returns the comparative rank and add the total number of numerical values and blanks. For example, for India, it add the text’s comparative rank in text (which is 1) and adds the number of blank cells and the number of numerical values.
Final Result – Sorted Data Now we will use these helper columns to get the sorted list. Here is the formula:
This method of sorting now becomes fool-proof. I have shown you the method for 8 items, but you can extend it to as many items as you want. Try it Yourself.. Download Example File
One Formula to Sort it All (without Helper Columns)
If you can handle extreme formulas, here is an all-in-one formula that will sort data in alphabetical order (without any helper column). Here is the formula: Enter this formula in a cell and drag it down to get the sorted list. Also, since this is an array formula, use Control + Shift + Enter instead of Enter. This formula has real-world utility. What do you think? I would love to learn from you. Leave your footprints in the comments section!
Multi-level Sorting in Excel How to Sort by Length in Excel? Easy Formulas! Step-by-step Guide to Use Sorting in Excel. Concatenate Excel Ranges (with and without separator). How to Sort Data in Excel using VBA (A Step-by-Step Guide). How to Sort By Color in Excel How to Sort by the Last Name in Excel
{=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL(IF(($A$2:$A$9″”)(MATCH($A$2:$A$9&””,$A$2:$A$9&””,0)=ROW($A$2:$A$9)-ROW($A$2)+1),IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9)))),ROWS($A$2:A2)),IF(($A$2:$A$9″”)(MATCH($A$2:$A$9&””,$A$2:$A$9&””,0)=ROW($A$2:$A$9)-ROW($A$2)+1),IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9)))),0)),"")} For reverse sorting data from ascending to descending replace SMALL( to LARGE( =If(a2=””,””,INDEX($A$2:$A$9,IFERROR(MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),0),MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),-1)) =IFERROR(INDEX($A$2:$A$9,MATCH(SMALL(NOT($A$2:$A$9=””)*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9))),ROWS($A$2:A2)+SUM(–ISBLANK($A$2:$A$9))),NOT($A$2:$A$9="")*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9))),0)),"") Thank you in the event of a reply! How would you work the above solution if there were names (first name and Surname of a person)? For example: David Li Matthew McLennan Darren Findlay Ben Maddahi David Currie None of the above methods seem to work (I am getting the rank formula value as 0) Could you help me with this? I have 100’s of students to sort this way each term and repeatedly using the “sort” function is tedious. Thank you =INDEX($A$2:$A$9,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),0)) Even better would be a formula that removes duplicates and sort the data alphabetically. This source data is text only. 2 – Duplicates still appear in the result column. How can I have it remove duplicates in the result column? Thank you! =IF(ISERROR(INDEX(‘Oct-Dec2017’!$B$3:$V$350,SMALL(IF(‘Oct-Dec2017’!$B$3:$B$392=MID(CELL(“filename”,X1),FIND(“]”,CELL(“filename”,X1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(‘Oct-Dec2017′!$B$3:$B$392)))),ROW(1:1)),MATCH(C$3,’Oct-Dec2017’!$B$3:$V$3,0))),””,INDEX(‘Oct-Dec2017’!$B$3:$V$392,SMALL(IF(‘Oct-Dec2017’!$B$3:$B$392=MID(CELL(“filename”,X1),FIND(“]”,CELL(“filename”,X1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(‘Oct-Dec2017′!$B$3:$B$392)))),ROW(1:1)),MATCH(C$3,’Oct-Dec2017’!$B$3:$V$3,0))) 50 A5 15/02/2017 stating something like this 10 B3 15/01/2017 178 A10 1/01/2017 178 A10 1/01/2017 and ending like so 10 B3 15/01/2017 50 A5 15/02/2017 I would think some sort of an array but any help would be most appreciated. Sort the above list in order of Letter and Number so that it looks like this: Letter Number A 4 A 5 A 7 B 3 B 7 B 8 One thing though, could you show me how to add a conditional filter to the match? I have a sorting array (thank you ) in “Sheet 2” that works perfectly to grab unsorted data from column “E” in the sheet “Level 4”, and then it sorts the data, drops any blanks and returns a list sorted by “Last Name”. What I need to add to this array formula is the filtering of data based on the “City” in column “G”. Here is my amended version of your formula: =IFERROR(INDEX(‘Level 4’!$C$2:$G$110,MATCH(SMALL(NOT(‘Level 4’!$E$2:$E$110=””)*IF(ISNUMBER(‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,”<="&‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,"<="&‘Level 4’!$E$2:$E$110)+SUM(–ISNUMBER(‘Level 4’!$E$2:$E$110))),ROWS(‘Level 4’!$E$2:$E2)+SUM(–ISBLANK(‘Level 4’!$E$2:$E$110))),NOT(‘Level 4’!$E$2:$E$110="")*IF(ISNUMBER(‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,"<="&‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,"<="&‘Level 4’!$E$2:$E$110)+SUM(–ISNUMBER(‘Level 4’!$E$2:$E$110))),0),3),"") This is what the unsorted data looks like on the “Level 4” sheet: C | D | E | F | G ——————————————————————- 1| id number | First Name | Last Name | N/A | City Where do I start? =IFERROR(INDEX(‘Level 4’!$C$2:$G$110,MATCH(SMALL(–NOT(‘Level 4’!$G$2:$G$110=”Rome”)*NOT(‘Level 4’!$E$2:$E$110=””)*IF(ISNUMBER(‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,”<="&‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,"<="&‘Level 4’!$E$2:$E$110)+SUM(–ISNUMBER(‘Level 4’!$E$2:$E$110))),ROWS(‘Level 4’!$E$2:$G2)+SUM(–ISBLANK(‘Level 4’!$E$2:$E$110))),–NOT(‘Level 4’!$G$2:$G$110=Key!$G$6)*NOT(‘Level 4’!$E$2:$E$110="")*IF(ISNUMBER(‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,"<="&‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,"<="&‘Level 4’!$E$2:$E$110)+SUM(–ISNUMBER(‘Level 4’!$E$2:$E$110))),0),3),"") Help! I’m spiraling into excel hell! Please help What if I want to sort cells with on Column A which greater than zero on Column C? Apple 1 Orange 1 Pear 0 Watermelon 2 Result: Apple 1 Orange 1 Watermelon 2 Is it possible to sort it with a simple formula but not array? Many thank! =IFERROR(INDEX($A$1:$A$15,MATCH(SMALL(IF(ISTEXT($A$1:$A$15),COUNTIF($A$1:$A$15,”<="&$A$1:$A$15)*15^2,IF(ISNUMBER($A$1:$A$15),COUNTIF($A$1:$A$15,"<="&$A$1:$A$15))),ROW(1:1)),IF(ISTEXT($A$1:$A$15),COUNTIF($A$1:$A$15,"<="&$A$1:$A$15)*15^2,IF(ISNUMBER($A$1:$A$15),COUNTIF($A$1:$A$15,"<="&$A$1:$A$15))),0)),"")
