Sorting 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
 
 

Leave a Reply
Reader Poll: Should the axis for bar charts always start at zero?  Sports Statistics Dashboard in Excel – Few More Alternatives 
82 Responses to “Sorting Text in Excel using Formulas”
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 nonunique 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 handdrawn. 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 nonunique 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…
Easier solution still:
Simply add in iferror and duplicate error values with the value that populates below. Nonunique 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.
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, nonVBA 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/whatisthefastestwaytoscanalargerangeinexcel.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 IFclause 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 nonunique 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 doubleedged 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/excelkpidashboardsort2/
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
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…
[…] 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 […]
Thank you very, very much. You really helped me a lot. I was looking for something like that for so long.
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…
@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/uniqueduplicatemissingitemsexcelhelp/
(2) now pass the second list to formulas in this post to get them sorted.. that is all..
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
thanks,really appreciate the very quick reply.
now i call that genius.
u guys rock.
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.
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.
@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.
Thank you,Sir.
I noticed that I wrote “…do it using functions,but what I meant was…using formulae”.
Waiting for your post.
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.
Very well done and very helpful. Thank you so much.
@Mike.. Thank you
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 nonunique 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
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
@War no: try http://datapigtechnologies.com/blog/index.php/sortingnumbersandtexttogether/
@chandoo
sorry, the link you give is look like to sorting data by “manual” not using formulas like from your web above.
war no
@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/sortingnumbersandtext.xlsx
Examine the formulas and modify them as you see fit.
dear boss Chandoo,
Great, that’s what i want. Thank you very much.
war no
supper techniqu……… thumps up buddy
Awesome find! Thanks for sharing.
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 nonunique 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 (NonUnique) – 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
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 countif 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 byebye to SMALL function.
The scenario posted by Robert for sorting nonunique records could also be solved using this method. Simply concatenate a sequence number behind the name and use countif on the helper column. This will eliminate the need to use the SMALL function.
Any more ways to solve this problem?
[…] 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 […]
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
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
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 formulabased, 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 likesorted 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.getdigitalhelp.com/2010/01/12/sortsvaluesinparallelarrayformula
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
HI
i have such as problem
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?
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 realphabetize 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.
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.
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.
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
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
2. My sorted numbers shall be in cells J29O29,
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.
[…] http://chandoo.org/wp/2008/10/22/sortingtextinexcelusingformulas/ […]
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.
Hi,
I have doen slight modification to the formula above to autoget the position of sorted numbers.
1. Consider my unsorted numbers in cell A1 – F1
2. My sorted numbers shall be in A2F2
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
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
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))))))
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..
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!
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??
This opens possiblities! Many thanks for this wonderful solution and your website in general!
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.
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
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/rumusexceluntukmenyusunnamasecara.html
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
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 zerolength 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.
hi
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
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 doublesort. 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.
Use my solution using “RANK”. I have listed it here in the comments
It is much slimmer and faster than Chandoo’s
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.
hi John Holt,
here’s my horizontal sort using RANK
it’s simpler, uses very much less resources and works for duplicates, triplicates etc !!
perhaps you could convert it to vertical ..
https://dl.dropboxusercontent.com/u/25664103/EXCELSORT.xls
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.
Give me some time to convert it to vertical..
OK i finally got it.
This is faster , slimmer and more resourceefficient than Chandoo’s solution.
Horizontal Sort:
https://dl.dropboxusercontent.com/u/25664103/HSort.xls
Vertical Sort:
https://dl.dropboxusercontent.com/u/25664103/VSort.xls
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.
Try this Text Vertical sort using RANK function.
https://dl.dropboxusercontent.com/u/25664103/TextVSort.xls
It compares first 3 letters – you could add more if needed.
@Joe M
Add a helper Column
In the helper column use
=Right(A1,Len(A1)Find(” “,A1))
Then sort by the helper column
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…