Sorting Text in Excel using Formulas

Posted on October 22nd, 2008 in Analytics , Featured , Learn Excel - 82 comments

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.

sorting-text-using-excel-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:

excel-countif-sorting-text-formulas

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

82 Responses to “Sorting Text in Excel using Formulas”

  1. Robert says:

    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.

  2. JT says:

    Love the RSS, Delicious, and stumble upon icons.

  3. Chandoo says:

    @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 :)

  4. Robert says:

    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…

    • Solmyre says:

      Easier solution still:

      Simply add in iferror and duplicate error values with the value that populates below.  Non-unique values cause an error in a specific direction leaving the last “duplicate” correctly populated (unless you reverse the search direction) so simply have your “errors” redirect to the cell below.  That way your duplicates will populate correctly without requiring you to create an additional column or using fractions etc.

      Example:Say you have your countif on column A, your values to sort on B and your sorted results on C.  Say value 3 is a duplicate and will generate an error, using iferror you can just redirect down to the result below and copy the duplicate value correctly.

      IFERROR(VLOOKUP(3,A1:B10,2,FALSE),C4))

      An actual forumla which makes far less sense looks like this:
      =IF($V11=””,””,IFERROR(VLOOKUP($V11,$T$11:$U$3012,2,FALSE),$X12))

      The point is, it uses iferror to point to cell x12 below it and gets past the duplicate problem with the “missing” value that V11 is looking at.

      This is currently the best method I have seen for sorting via formula.  I’m sure there is a way to break it, but I haven’t encountered it yet.

  5. Frederick says:

    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!

  6. Robert says:

    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.

  7. Chandoo says:

    @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… :D 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

  8. derek says:

    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” :-)

  9. derek says:

    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.

  10. Robert says:

    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!!

  11. Rajesh says:

    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.

  12. Frederick says:

    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!

  13. Chandoo says:

    @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. :D

    Keep in touch…

  14. David says:

    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!

  15. Chandoo says:

    @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 :)

  16. Ketan says:

    @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.

  17. David says:

    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

  18. Social comments and analytics for this post…

    This post was mentioned on Twitter by r1c1: @lohhw3 oops..my mistake, I think you need to use formulas to sort numbers and text. learn more: http://bit.ly/q1zw9

  19. […] on Mar.27, 2009.  Email This article to a Friend I am inspired once again by the article Sorting Text in Excel using Formulas at Pointy haired Dilbert. In Chandoo´s article he sorts text with a "helper" column. My goal with […]

  20. Rifat Ahmed says:

    Thank you very, very much. You really helped me a lot. I was looking for something like that for so long.

  21. badprogeny says:

    thanks man,you guys rock.
    i’ve been looking for something like this for more than a year now.
    now u cured my headache.
    but i still have something to ask.
    i have a list which includes repeating names.
    something like the following:
    1.Ronald Reagan
    2.George Bush
    3.Bill Clinton
    4.Barrack Obama
    5.George Bush
    6.Bill Clinton
    after i sorted it with ur algorithm,
    i found that
    1.Barrack Obama
    2.Bill Clinton
    3.Bill Clinton
    4.George Bush
    5.George Bush
    6.Ronald Reagan
    now what i want is just:
    1.Barrack Obama
    2.Bill Clinton
    3.George Bush
    4.Ronald Reagan
    i’m at my wit’s end figuring out how to count only once.
    i tried rank and countif with any possible way that i could think of.
    there should be a way to do it without resorting to vba.
    thanks in advance.
    this is not directly related to ur sorting algorithm but
    it’s partly related i guess because what if the sort list contains repeating entries…

    • Chandoo says:

      @Badprogeny… thank you so much for your comment and Welcome to Chandoo.org.

      You can get rid of duplicates and then sort using below technique.

      (1) first get rid of all duplicate and create a new list in a separate column – see this: http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/
      (2) now pass the second list to formulas in this post to get them sorted.. that is all..

    • mohammad says:

      hi , and thanks alot for this helpful guide
      i have same problem like this so i changed the formula for Sort Order in above with this :
      Sum(countif($c$6:$C$15,”<="&C6),-countif(c6:$C$15,"<="&C6)
      if you want to keep duplicated
      and this :
      if(countif(C6:$C$15,"<="&C6)=1,Sum(countif($c$6:$C$15,"<="&C6),-countif(c6:$C$15,"<="&C6),"")
      if you dont want to keep duplicated

  22. badprogeny says:

    thanks,really appreciate the very quick reply.
    now i call that genius.
    u guys rock.

  23. badprogeny says:

    Now that I am asking for your help,let me ask again.
    I already googled and tried it out in excel,but can’t find what I want,which is:
    I have 2 columns,which have repeating names in each column,
    I want to get an algorithm to merge these 2 columns into one retaining
    only a single instance of same names.
    Column A Column B Column C
    1.Bill Clinton John Travolta Bill Clinton
    2.Barrack Obama Denzel Washington Barrack Obama
    3.George Washington Bill Clinton George Washington
    4.Abraham Lincoln Paris Hilton Abraham Lincoln
    5.Paris Hilton Paris Hilton
    6. John Travolta
    7. Denzel Washington

    I’m sorry if it’s easily achievable and stupid,but I can’t figure out how to do it using functions…
    Thanks in advance.

  24. badprogeny says:

    I’m sorry,the words didn’t retain the formatting,
    let me explain again.
    I have 2 columns,which have repeating names in each column,
    I want to get an algorithm to merge these 2 columns into one retaining
    only a single instance of same names.

    Column A
    1.Bill Clinton
    2.Barrack Obama
    3.George Washington
    4.Abraham Lincoln

    Column B
    1.George Washington
    2.John Travolta
    3.Denzel Washington
    4.Bill Clinton
    5.Paris Hilton
    6.Abraham Lincoln

    Column C
    1.Bill Clinton
    2.Barrack Obama
    3.George Washington
    4.Abraham Lincoln
    5.John Travolta
    6.Denzel Washington
    7.Paris Hilton

    I’m sorry if it’s easily achievable and stupid,but I can’t figure out how to do it using functions…
    Thanks in advance.

  25. Chandoo says:

    @Badprogeny.. very good and interesting question. I will come up with a macro or formula based approach for this and post on the blog soon. Keep an eye.

  26. badprogeny says:

    Thank you,Sir.
    I noticed that I wrote “…do it using functions,but what I meant was…using formulae”.
    Waiting for your post.

  27. roman p says:

    If you have duplicate items, determine the right index by subtracting the number of identical, preceding rows: =COUNTIF(SORT RANGE, “<="&CURRENT CELL)-COUNTIF(MODIFIED SORT RANGE,"="&CURRENT CELL where MODIFIED SORT RANGE includes the SORT RANGE except the portion below the current cell, ie. $F$3:$F6 assuming the sort range starts at F3 and the current cell is F6.

  28. Mike says:

    Very well done and very helpful. Thank you so much.

  29. Gal says:

    Chandoo,

    Thank you so much for your blog and information. I find everything here extremely helpful. My question is about a sort using two columns (or more!) as key, eg. i have one column of non-unique and unique text (let’s say {apple, bananna, plum, banana, plum, plum, apple, apple, plum}), and then another column which is uniques values (ie prices). How would i first sort by the first column (which is virtually grouping because I have many similar values), but then sort from big to small? (i will get first all the apples from expensive to cheap, then bananas and then plums)

    thanks!
    Gal

  30. war no says:

    dear all,
    i tried this formula on my data that contains: 1,3,4,M1,M27,M16,M31,12,57,216.
    it doesn’t work, especially on cell contain combined text and number like M1, etc.
    how i modify this formula for my above data?
    thanks and regards,
    war no

  31. war no says:

    @chandoo
    sorry, the link you give is look like to sorting data by “manual” not using formulas like from your web above.

    war no

  32. Chandoo says:

    @War No.. you can modify the formulas slightly to solve this. But the definition of sort order varies alot when you are talking about numbers and text mixed. I have put together an example here: http://chandoo.org/img/playground/sorting-numbers-and-text.xlsx

    Examine the formulas and modify them as you see fit.

  33. war no says:

    dear boss Chandoo,

    Great, that’s what i want. Thank you very much.
    war no

  34. febin says:

    supper techniqu……… thumps up buddy

  35. Ben Lam says:

    Awesome find! Thanks for sharing.

  36. Chiga says:

    I stumbled upon this forum and this is like treasure island. Great work guys.
    I’m stuck at a similar issue and I think you would know a solution.
    I’m trying to sort on a non-unique column but also fetching the corresponding unique value
    Here is an example. Sorting on Part Name (COL B) :
    COL A Part # (UNIQUE) – 9,5,1,7,4,6
    COL B Part Name (Non-Unique) – Buffy,Genius,Cherry,Cherry,Sharpie,Amy
    Basically part 1 and part 7 are both called Cherry.
    COL C – I executed the COUNTIF formula on col B as discussed. Resulting values – 2,5,4,4,6,1
    COL D – VLOOKUP for Part # using the small function yeilds 6,9,1,1,5,4
    I’m missing 7 for Cherry. Is there a way to overcome this without ceding to VBA ?

    Thanks

  37. Chiga says:

    Right after I posted my problem on this forum a bulb lit up. I guess the collaborative brain power on this forum helped clear the clutter in my head.

    I used a helper col E to concatenate Col B & Col A so that each row becomes unique. COL D – Buffy9, Genius5, Cherry1, Cherry7, Sharpie4, Amy6. I executed the count-if on the helper col instead of the Part Name. Resulting values – 2,5,3,4,6,1. Now I could do an easy VLOOKUP to fetch both my original Part # and Part Name and said bye-bye to SMALL function.

    The scenario posted by Robert for sorting non-unique records could also be solved using this method. Simply concatenate a sequence number behind the name and use count-if on the helper column. This will eliminate the need to use the SMALL function.

    Any more ways to solve this problem?

  38. […] is the tricky part. I have used COUNTIF formula to sort the list. Learn how to sort a list of values using formulas [More on sorting […]

  39. Pablo says:

    Hi Chandoo,
    You made a good point, there is no formula to sort, but using VBA, we can create a function that does it us. So I came up with 2 approaches.
    The 1st one “sortme” requires the range and the position number in the sorted list, in this way if you copy the formula down, starting with position 1, you will get a sorted list.
    The 2nd one “sortme1″ requires the range and the order, 0 for ascending and 1 for descending. The results come up in the same cell with ; separator (a function limitation as far as try).
    Here is the code:

    Function Sortme(miRango As Range, Optional ByVal Order As Integer) As String
    If Order = 0 Then Order = 1
    For Each cell In miRango
    temp1 = Application.WorksheetFunction.CountIf(miRango, “<=" & cell.Value)
    If Order = temp1 Then Exit For
    Next cell
    Sortme = cell.Value
    End Function

    Function Sortme1(miRango As Range, Optional ByVal Order As Integer) As String
    Dim Resp(), C, Largo, temp1, temp2
    Largo = miRango.Rows.Count
    ReDim Resp(Largo)
    C = 0
    temp2 = ""
    For Each cell In miRango
    C = C + 1
    temp1 = Application.WorksheetFunction.CountIf(miRango, "<=" & cell.Value)
    Resp(temp1) = cell.Value
    Next cell
    For I = 1 To C
    If Order = 0 Then
    temp2 = temp2 & Resp(I) & ";"
    Else
    temp2 = Resp(I) & ";" & temp2
    End If
    Next I
    Sortme1 = temp2
    End Function

    Enjoy,
    Pablo

  40. Mike L. says:

    Hi,
    I stumbled upon your page while searching for a formula that will take a column of names, LAST, FIRST, (there are gaps between some names) in a spreadsheet, and display them in alphabetical order (with no gaps between) in a different part of the spreadsheet. I am not well versed in formulas, if you recommend a formula, please explain in simple terms. Thank you in advance. Mike

  41. Markosys says:

    I’m new to excel array techniques (and loving it), so please forgive me if my question’s solution is obvious, but your tip goes a long way toward answering it.

    I need to perform an approximate match upon an unsorted table and then lookup an associated value. For your example, maybe add a approval rating column. Because the unsorted table is dynamic and will gain many new rows over time, I need the solution to be formula-based, with no intermediate tables.

    Is there a way to build upon your tip to have the sorted result in memory as an array that then could be applied to the related column? My current strategy would be to use the LOOKUP vector function upon the two like-sorted arrays.

    I’ve learned from some other sources, but am having a difficult time knitting it together (see links below).

    Many Thanks!

    Mr Excel & excelisfun Trick 36: VLOOKUP w Approximate Match & Unsorted Table?http://www.youtube.com/watch?v=rxhL72gvM5E

    Sort values in parallel (array formula)
    http://www.get-digital-help.com/2010/01/12/sorts-values-in-parallel-array-formula

  42. Anonymous says:

    here is what I am getting can someone help?

    Sort Order Info needing Sorting Sorted Info
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous #N/A
    11 Anonymous Anonymous

  43. David G says:

    Great formula. I’m trying to take it one step further, but need some help.

    I have a long list of items, in 3 different categories (i.e. Column A is all unique values, Column B is 1 of 3 values). I have 3 different tabs, one for each category, where I want an alphabetized list.

    How can I alter this formula so that I get numerical order among only the same category? That way I can use an IF formula combined with a VLOOKUP to get alphabetized lists on each category tab.

    Here’s an example list:

    Banana | Fruit
    Grape | Fruit
    Pork | Meat
    Carrot | Vegetable
    Beef | Meat
    Onion | Vegetable

    How do I make a formula to get Banana, Beef, and Carrot to all be 1 and the rest all be 2?

    • David G says:

      By the way, I don’t want to manually sort the list as I want to be able to insert new items to the list and have everything re-alphabetize without. Basically, I should be able to data enter on the data tab and have all the other 3 tabs resort after each new entry.

    • David G says:

      Got it, but perhaps there is a simpler process:

      1) I made a new column with formulas =CATEGORY&ITEM to combine the 2 terms (result FruitBanana, etc.)
      2) I used the original formula to get an order for all this new merged text.
      3) I made column that has this formula =COUNTIF(CATEGORY COLUMN,”<"&CATEGORY CELL). It returned zeroes for the first category items, the number of first category items for the second category items, and the number of first and second catgory items for the third category items.
      4) The difference between #2 and #3 gets me the answer

      Obviously I'll combine formulas in 2) and 3) to get 4) with less columns. I can possibly even merge 1) as well so I don't need that column, but I'll have to play around with that.

      Essentially the formula is = ORDER OF EVERYTHING – NUMBER OF THINGS THAT FALL IN OTHER CATEGORIES THAT COME FIRST ALPHABETICALLY.

  44. Noor says:

    I need to ask help about my problem for sorting.
    I am making the NCR report and want to take data from 1 table to other, such as:

    Table 1 ( April 2012)
    Date NCR #
    1 101
    2 101
    2 105
    3 502
    4 502
    4 205

    And put the above data to calendar table to make the graph :

    Table 2: Date
    1 2 3 4 5 6 7 .. 30
    NCR#
    101 1 1
    105 1
    205 1
    502 1 1

    Please help.

    Thanks in advance.

  45. Gameguy says:

    Dear sir,

    It is very useful for sorting, but i tested my case if the cell is number in text format. eg. ‘123456.

    Seem the formula doesn’t works, could you teach me ?

    Many tks.

    Gameguy

  46. Ariel Wong says:

    My solution is much simpler.
    1. My unsorted numbers ( or words ) are listed horizontally. e.g. B29 – G29  ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function :D
    2. My sorted numbers shall be in cells J29-O29,
    3. The formula for cell J29 is
    =IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))
    3. The formula for cell K29 is … just convert all the  “=1″ into “=2″
    4. The formula for the rest is  “=3″ for L29 and so on till “=6″ for O29.
    5. The RANK function will rank every cell in the range. There will not be any unranked. The last part  .. ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29)
    If there are more than one same number  .. meaning there are more than one number of the same rank.. it would duplicated the first number of the same rank.

    Hope this would help u guys.
     

  47. david says:

    Genius. Originally I accomplished the same thing with big, ugly use of INDIRECT, but the source worbook was to be used in within Xcelsius, which doesn’t support INDIRECT. Not only does this solution work in Xcelsius, it hogs a heck of a lot fewer resources than my original idea. Five points for you, sir.

    Final formula added a check for blanks, assigning them 999 to force them off the final list of around 100; and then a reset to subtract the number of blanks from the final list. Works beautimous.

    =IF(B5=””,999,COUNTIF($B$5:$B$76,”<=”&B5)-COUNTIF($B$5:$B$76,””))

    Thanks for the help. May a giant cookie soon come into your life.

  48. Ariel Wong says:

    Hi,
    I have doen slight modification to the formula above to auto-get the position of sorted numbers.
    1. Consider my unsorted numbers in cell A1 – F1
    2. My sorted numbers shall be in A2-F2
    3. For cell A2 the formula is =IF(RANK($A1,$A1:$F1,1)=COLUMN(A1),A1,IF(RANK($B1,$A1:$F1,1)=COLUMN(A1),$B1,IF(RANK($C1,$A1:$F1,1)=COLUMN(A1),C1,IF(RANK($D1,$A1:$F1,1)=COLUMN(A1),$D1,IF(RANK($E1,$A1:$F1,1)=COLUMN(A1),$E1,IF(RANK($F1,$A1:$F1,1)=COLUMN(A1),$F1))))))
    there is no need to check for duplicates for the first cell
    4. For cell B2 the formula is slightly different at the end..
    =IF(RANK($A1,$A1:$F1,1)=COLUMN(B1),B1,IF(RANK($B1,$A1:$F1,1)=COLUMN(B1),$B1,IF(RANK($C1,$A1:$F1,1)=COLUMN(B1),D1,IF(RANK($D1,$A1:$F1,1)=COLUMN(B1),$D1,IF(RANK($E1,$A1:$F1,1)=COLUMN(B1),$E1,IF(RANK($F1,$A1:$F1,1)=COLUMN(B1),$F1,A1))))))
    there is an additional “=COLUMN(B1),$F1,A1))))))”
    If there are numbers of same rank, the prior number shall be duplicated .
     
    Hope this is better . thanks

    • Ariel Wong says:

      Arrggh.. the FULL formula for A2.
      =IF(RANK($A1,$A1:$F1,1)=COLUMN(A1),$A1,
      IF(RANK($B1,$A1:$F1,1)=COLUMN(A1),$B1,
      IF(RANK($C1,$A1:$F1,1)=COLUMN(A1),$C1,
      IF(RANK($D1,$A1:$F1,1)=COLUMN(A1),$D1,
      IF(RANK($E1,$A1:$F1,1)=COLUMN(A1),$E1,
      IF(RANK($F1,$A1:$F1,1)=COLUMN(A1),$F1))))))
       
      And the full formula for cell B2 ..
      =IF(RANK($A1,$A1:$F1,1)=COLUMN(B1),$A1,
      IF(RANK($B1,$A1:$F1,1)=COLUMN(B1),$B1,
      IF(RANK($C1,$A1:$F1,1)=COLUMN(B1),$C1,
      IF(RANK($D1,$A1:$F1,1)=COLUMN(B1),$D1,
      IF(RANK($E1,$A1:$F1,1)=COLUMN(B1),$E1,
      IF(RANK($F1,$A1:$F1,1)=COLUMN(B1),$F1,B2))))))
       
      Just change this “COLUMN(B1)” to “COLUMN(C1)” for the third cell and so on

      • Ariel Wong says:

        Correction for cell B2 should be ..
        =IF(RANK($A1,$A1:$F1,1)=COLUMN(B1),$A1,
        IF(RANK($B1,$A1:$F1,1)=COLUMN(B1),$B1,
        IF(RANK($C1,$A1:$F1,1)=COLUMN(B1),$C1,
        IF(RANK($D1,$A1:$F1,1)=COLUMN(B1),$D1,
        IF(RANK($E1,$A1:$F1,1)=COLUMN(B1),$E1,
        IF(RANK($F1,$A1:$F1,1)=COLUMN(B1),$F1,A2))))))
         
        the last part , in event of a duplicate number should duplicate the prior number ..
        IF(RANK($F1,$A1:$F1,1)=COLUMN(B1),$F1,A2))))))

        • Ariel Wong says:

          Additional correction
          make sure you change the last part too..
          for Cell B2 .. IF(RANK($F1,$A1:$F1,1)=COLUMN(B1),$F1,A2))))))
          for Cell C2 .. IF(RANK($F1,$A1:$F1,1)=COLUMN(B1),$F1,B2))))))
          for Cell D2 .. IF(RANK($F1,$A1:$F1,1)=COLUMN(B1),$F1,C2))))))
          and so on..

  49. Javier says:

    I cannot find the words to thank you.
    This solution is so obvious… I cannot believe how many years using a much more complex solution (VBA).
    Thanks!

  50. Akshay says:

    This is the inspiration I needed for what I was trying to do. Thank you.
    Last but not the least, Sarah Palin and Paris Hilton is just not right in this group or we can create a new group with Kardashians in there as well. Opinion?? :)

  51. Ruben says:

    This opens possiblities! Many thanks for this wonderful solution and your website in general!

  52. Deepak Sugandhi says:

    The array formula I was using was very slow & using lot of resorces (My data has around 1000 rows). This is lot faster. Thanks Chandoo.

  53. ali says:

    Dear,
    i want that if i will put the figure ( amount ) in a row so automatically text of that amount should come on another row of column,
    like :- 3555/- so in text three thousand five hundred fifty five only.
    help me to make a formula.
    thank you

  54. NK says:

    I have a sheet containing formula for automatically sorting text data (e.g. names), you can download it from:
    http://www.nanakasep.blogspot.com/2014/01/rumus-excel-untuk-menyusun-nama-secara.html

  55. El Bee says:

    I just found this blog; interesting use of vlookup and countif but this doesn’t work if you have a fluctuating table of names. I have a column that can hold up to 20 names and your example came back with #N/A because I was using the entire table which had several blank cells. If I reduced my table to just include those cells with values other than “” then the formulas work. But since I use a link to another spreadsheet to load my table it may have 5 names one day, 13 another day, and maybe eve 20 another day.
    Is there a work around for this or do I need to search for another solution? The standard sort function also fails to sort correctly because of the empty cells.
    FYI: my table uses a Name and I use that Name in your formulas.

    Thanks for your time,

    El Bee

    • DaveS says:

      You can get rid of the #N/A by embedding the simple VLOOKUP in an IF statement with the ISNA function, which enables you to substitute a zero-length string; for example

      =IF(ISNA(VLOOKUP(A3,$B$3:$C$10,2,FALSE)),””,VLOOKUP(A3,$B$3:$C$10,2,FALSE))

      And for variable numbers of names, if you know what the maximum is ever likely to be you can set the lookup range ($B$3:$C$10 in the above example) for that maximum number of rows, and insert the COUNTIF and (modified) VLOOKUP formulas for all these rows. The COUNTIF will return 0 for missing data, and the modified VLOOKUP will sort the data correctly.

  56. Harshawardhan says:

    Hi,
    i downloaded the workbook given above for sorting example.
    That fails if you have two similar names.
    If there are two names say “Chandoo” then only one comes and one “#N/A” comes.
    Can someone help with the correction pls

  57. John Holt says:

    This has helped me such a lot. I’ve used it extensively.
    However, I can’t get the formulas shown here to ignore the duplicates in the text list. Using the ‘remove duplicates from a list methodology that Chandoo’s shown doesn’t quite fit with what I wanted to do. Sorry, Chandoo.
    When I tried to use the formula shown earlier in this post to ignore the duplicates: if(countif(C6:$C$15,”<="&C6)=1,Sum(countif($c$6:$C$15,"<="&C6),-countif(c6:$C$15,"<="&C6),""), I got a FALSE. (I think there's an erroneous comma in there somewhere but I don't think that's the reason for the FALSE).
    My solution was to double-sort. Sort once to get the sorted list. This will also return some blank lines as the formula only returns, e.g., one '10'. Any other '10' gets a blank line. Then it's easy to sort again as the formula can be amended to insert a zero when there's a blank line.
    But, a slight problem. If the list to be sorted contains formulas, inserting data copied from another data source, and those formulas are constructed to return a "" value if there's a blank line in the data source, Chandoo's solution counts the cells containing formulas as part of the sorted list so the numbers assigned to the list to be sorted are incorrect.
    I've tried various methods to counter this from testing the cell in the list to be sorted to see if it contains a value greater than "A" to testing for not being equal to a value of “?*” with mixed success.
    Any thoughts welcome.

  58. John Holt says:

    Thanks for this response, Ariel.
    Apologies if I’m confused here but what spreadsheet are you using as the basis for your formulas ?
    Chandoo’s example spreadsheet at the top of this thread only contains columns A and B.
    Your formula references a range containing column F. Should this be changed to column B ?
    Apologies again.

  59. John Holt says:

    Sorry, Ariel, I was confused. Having read all of your post, I realise that you’re referring to a completely different example.
    Having understood that, I’d be grateful if you could post how your formulas could be modified for a vertical, rather than horizontal, list.

  60. Joe M says:

    Can someone help me, I have a list of address that include numbers in one cell for eg.

    110 Great South road = 1 Cell

    I want to know how to sort these cells in alphabetic order using the street name no the numbers??

    Thank you in advance.

  61. Candy says:

    Hi Chandoo,
    i opened worksheet for countif , any wondering why 10 is for ‘steve jobs’ while 9 is for Steve Balmar, i thought steve Balma is smaller than Steve Jobs…

Leave a Reply