SubscribeSorting Text in Excel using Formulas
Sorting text is such a day to day activity that it always surprises me why Excel hasn’t provided a simple spreadsheet formula for doing it. Of course you can use the sorting menu command (menu > data > sort) but this requires manual steps (or VBA). Most of the times we get raw textual data from various sources and we need it to be sorted. While fooling with the COUNTIF() formula, I have realized a powerful yet little known feature that can be exploited to sort text using formulas.
We all know that countif() can be used to find the number of cells in a given range matching a criteria. But do you know that you can use COUNTIF() to find the number of cells in a range greater than or less than a particular value?
Well, that is the trick to sorting text. How?
For eg. assume range A1:A10 has c,b,d,f,h,j,e,a,i,g in them. When you write =countif(a1:a10,"<c") you will get 2 as the result.There are 2 cells with value less than “c”. In other words, the sort order of “c” in the given cells is 3 (since it has 2 cells less than “c”)
You can use this on your own list to fetch the alphabetical sort order of each text value like this:
Essentially the sort order formula looks like this: =countif(SORT RANGE, "<="&CURRENT CELL)
Once you have the sort order, arranging the cells in that order is a piece of cake. We just use VLOOKUP to do our job, like this: =VLOOKUP(1,SORT ORDER TABLE,2,FALSE). (PS: if you are worried about unique cells, which you should, then use this formula instead, =VLOOKUP(small(SORT ORDER COLUMN,1),SORT ORDER TABLE,2,FALSE)
What the heck is above formula doing? It is running a vlookup on the table containing original cells and their sort order to fetch the cell with sort order 1 (or the smallest sort order). Replace the 1 with 2 to get the next cell in the alphabetical order.
Download the workbook with alphabetical text sorting using formulas and see this in action.
Bonus tip: Instead of rewriting the vlookup formula with 2, 3, 4 as lookup value you can use excel's row() function to generate those running numbers for you. You just need to subtract correct value from the row().
Also read: Shuffling a list of cells in random order using formulas, More analytics / text processing tweaks
Trackbacks & Pingbacks
- Trackback by uberVU - social comments on October 27, 2009 @ 5:14 am
- Pingback by Sorting text cells using array formula in excel on November 3, 2009 @ 2:11 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Chandoo,
very nice trick for sorting texts without array formulas. I have never seen this one before.
There is one shortfall though: The technique only works with a list of unique items.
But I guess you could easily solve this problem in a similar way we did it in the 2nd post of the KPI dashboard series for numbers.
Love the RSS, Delicious, and stumble upon icons.
@Robert: Thanks…
I guess it works the same way with non-unique values as long as you are fetching the sorted values.
But if you need to fetch any other table data based on the sort order of the value, then you we have to use your method of adding a small fraction to the sort order value to ensure that it is unique.
@ JT: thanks, they are hand-drawn. Feel free to copy or share them
Chandoo,
sorry, but the technique in the workbook you posted for download does not work for a list with non-unique values:
E.g. overwrite the second entry in your list (Bill Gates) with the first one (Steve Jobs). The sort order number will then be 10 for the first two rows of the list, there will be no number 9 in the sort order column and the VLOOKUP fails when looking for number 9.
As mentioned you can easily overcome this by adding a help column with formulas that add very small, unique fractions to the text.
Nevertheless: using COUNTIF for sorting texts is a very clever idea and technique. Thanks for sharing.
Oh, by the way: I’m a PC. Replacing Bill Gates by Steve Jobs was just an example…
Hi PHD,
I am very new to Excel and your site. I must say that alot of stuff I have seen on this site and other other sites linked has really opened my eyes to the possibilities of Excel.
Regarding this technique, I have some questions:
1) Your technique is perfect if the list is small as only a very small amount of formulas (COUNTIF, VLOOKUP) are required to execute it.
2) I fiddled with your file for a long while before I came up with an alternative using MATCH and OFFSET (inspired by Roberts earlier post on Dashboard) – Hence my question is: if the list is huge (e.g 50,000 records and we all know how VLOOKUP freezes up over huge databases) would my method require less resources than yours?
3) I still have not figured a way to cater for unique records using my method…
Still like to say thanks for all the posts which is contributing to my appreciation of creativity with Excel!
Frederick,
I have never checked but I doubt MATCH and OFFSET will use less resources and speed up calculations compared to a VLOOKUP solution. I can’t think of any formula based, non-VBA solution that sorts 50.000 records in reasonable time.
If you really have 50.000 records, I recommend to store the data in a database (MySQL, Access, etc.), create queries in the database to consolidate and sort the data and retrieve the data in Excel from the database.
@Robert, you are right. Actually there is a simple workaround for unique cells problem.
Inseted of using running numbers like 1,2,3… you can use small() function to fetch the nth smallest number in the list and then use it in vlookup. This was the original solution I had used. But then I removed the small() to make it easier to understand not realizing the damage it creates for the unique cells. One reasons why I said it works for unique cells…
dumb me…
Here is how I would write the formula:
=VLOOKUP(small(SORT ORDER COLUMN,1),SORT ORDER TABLE,2,FALSE).
This works well, since small would return 10 after 8 if both 9 and 10 rows are same. I will edit the post and add a note about this.
@Frederick: Welcome to PHD site and thanks for taking time to comment.
As Robert said, the best way is to use VBA or some DB method. I wouldnt trust excel with 50000 records for simpler formulas, not to mention the sorting or lookups. I am not sure how much performance improvements match / offset would give either.
However if you are planning to write vba do check out this post on scanning large ranges, it may help you in writing optimum code: http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx
Robert,
If you replace
ROW()-ROW($E$5)
with
SMALL($B$6:$B$15,ROW()-ROW($E$5))
it works again! Because the SMALL formula looks for the ninth smallest value, which is “10″
Struck by inspiration, I tried SMALL() all on its own with text values, as a possible way of cutting through all the complication.
Sadly, unlike COUNTIF(), SMALL() does not accept strings. I see no reason why it could not have, if MS had thought about it. It would then be the “simple spreadsheet formula” Chandoo wished for.
Chandoo, Derek,
thanks for the tips. This is fantastic.
Using COUNTIF and VLOOKUP for formula based sorting is much more elegant than the technique I used in the KPI dashboard workbook.
And it works with numbers and texts. As derek pointed, the technique using SMALL or LARGE (see KPI dashboard post nr. 2) works with numbers only.
You could easily add an IF-clause into the COUNTIF formula to switch between >= and <= and let the user toggle the sort order.
A really great idea, Chandoo. Thanks for sharing!!
Hi solution to solve the non-unique problem that you foresee in the formula given above
Use “=COUNTIF($A$2:$A$7,”<”&A2)+1″ instead of the formula above
I bet it works.
I would like to thank the person who has devised the formula as it an excellent one! If it doesn’t work, give me a slap by emailing me.
Hi PHD/Robert,
Thanks for the feedback. I never touched VBA or macros in my life so all that I know of Excel are just formulas…
Thankfully, I don’t have to manage large databases at this point in time… Anyway, I hope I will be able to learn more about creative uses of Excel formulas here!
Cheers!
@Derek: You are right, using small() or large() should solve the problem. I have updated the post with this. Thanks. Btw, how nice it would be if MS had actually enabled small/large for text. But….
@Robert… thanks so much for the wonderful words.
@Rajesh: Welcome to PHD blog and thanks for comments
I am not sure how adding 1 to countif() can make the sort order counts unique. instead of having two 9s we will now have two 10s. Can you explain?
@Frederick: VBA is a double-edged sword. Good thing you havent ventured in to it. I have barely used VBA in my first 4 years with excel and even today I get a mild sense of panic while hitting ALT+F11 to open that code window.
Keep in touch…
What if I want to sort the following:
Value, Rank
1,2
2,3
3,1
4,4
5,4
It should be:
Value,Rank
3,1
1,2
2,3
4,4
5,4
How do I get this to work? I get 4,4 and 4,4 for the last two rows. Help!
@David: You can make excel return unique rows by adding a very small random fraction to the original values or running fractions to the original values. We have used similar technique in our KPI dashboards to ensure that each row is unique. You may want to check it out…
http://chandoo.org/wp/2008/08/27/excel-kpi-dashboard-sort-2/
Let me know if you still have some doubts… I suggest you download the files provided in that post and see how the formulas are written
@David :
Insert your data, say, in D6 to D10 cell.
Insert one more coln. say “E”.
Put the formula “MID(D6,FIND(”,”,D6)+1,10)” in cell E6 and copy till E10.
Select Range D6 :E10 and sort by col E (Ascending) you will get desired result.
Thank you Chandoo and Ketan. You guys(/gals?) rock. Since I found this website a few weeks ago I have learned SOOOOOO much. I look forward to learning much more and being able to contribute sometime as well.
David