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
106 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 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…
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.
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
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/unique-duplicate-missing-items-excel-help/
(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 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
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/sorting-numbers-and-text-together/
@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/sorting-numbers-and-text.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 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
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?
[...] 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 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
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 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.
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 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.
[...] http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/ [...]
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 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
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/rumus-excel-untuk-menyusun-nama-secara.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 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.
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 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.
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 resource-efficient than Chandoo's solution.
Horizontal Sort:
https://dl.dropboxusercontent.com/u/25664103/H-Sort.xls
Vertical Sort:
https://dl.dropboxusercontent.com/u/25664103/V-Sort.xls
Any chance of re-posting that file for formulae somewhere?
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/Text-V-Sort.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...
Sorting with formulas.. That was really nice tutorial. I have never used this trick, now applying sorting in excel with countif. thanks for tutorial.
Here's my solution.
In the sheet 'Team List Data':
In column F I have my list, which has many duplicate values.
I want to create a sorted list of only unique values.
In column AI I use the formula:
=IF(COUNTIF($F$1:$F2,$F2)=1,COUNTIF($F:$F,"<="&$F2),"")
which is dragged down to fill column AI.
On another sheet 'Teams', I have my sorted list.
The numbers 1 to 99 in column B.
In column C I have the following formula:
=IFERROR(INDEX('Team List Data'!$F:$F,MATCH(SMALL('Team List Data'!$AI:$AI,Teams!$B2),'Team List Data'!AI:AI,0)),"")
which is dragged down to fill column C.
Explained:
In sheet 'Team List Data', column AI:
o IF(COUNTIF($F$1:$F2,$F2)=1, ...
Build a range from $F$(firstrow) to $F(currentrow), and count the number of times that the current cell, for example, $F2 appears.
If it has only appeared once (i.e. its first appearance), proceed:
o ... COUNTIF($F:$F,"<="&$F2), ...
Count the number of entries in column F that are less than or equal to the current cell $F2, and return that value.
o ... "")
However, if it has appeared more than once (i.e. it is not the first appearance), return a blank value.
=
IFERROR(
INDEX('Team List Data'!$F:$F,
MATCH(
SMALL('Team List Data'!$AI:$AI,Teams!$B2)
,'Team List Data'!AI:AI,0)
)
,"")
From the inside, out:
o [a] = SMALL('Team List Data'!$AI:$AI,Teams!$B2),
For example, where Teams!$B2 = 1 (the first item in my sorted list), find the 1st smallest value [a] in sheet 'Team List Data', column AI.
o [b] = MATCH( [a] ,'Team List Data'!AI:AI,0)
Take this value [a], and using MATCH, find the row number [b] where this value occurs.
o [c] = INDEX('Team List Data'!$F:$F, [b] )
Looking in my original list (sheet 'Team List Data', column F), take the value [c] at the row number [b].
o IFERROR( [c] ,"")
If the above fails (i.e. where the number in Teams!$B(listrow) is greater than the number of unique values in the list), return a blank value (instead of returning #NUM!).
NOTE:
Instead of using the number column in Teams!$B:$B, you could use the ROW() trick. Assuming your sorted list starts on the second row,
Replace Teams!$B2 with ROW()-ROW($1:$1)
=IFERROR(INDEX('Team List Data'!$F:$F,MATCH(SMALL('Team List Data'!$AI:$AI,ROW()-ROW($1:$1)),'Team List Data'!AI:AI,0)),"")
http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/
[…] start with explaining how to get the index of each name (concept from Chandoo.org): Get index of elements in our unsorted distinct list Notice the code in column […]
Hi,
How if i use it for the list that i've formatted as table (i use ms office 2010).
It works only if all row filled. But if i add blank row below (i.e 4 blank row), its not started from 1, but started from 5.
Here is the formula i've used
=COUNTIF([Names];"<="&[@[Names]])
Is there any solution ?
So i get sort number started from 1..
thanks..
I have a scenarios as;
1 Apple 10
2 Mango 20
3 Orange 30
4 Mango 40
5 Apple 50
6 Mango 60
7 Orange 70
8 Mango 80
9 Apple 90
10 Mango 100
I want to solve the data by sorting it using a formula that first column shows apple and quantity against apple second column will see if any more apples are there in the column than again it shall give me the answer apple with its quantity as displayed below;
1 Apple 10
5 Apple 50
9 Apple 90
2 Mango 20
4 Mango 40
6 Mango 60
8 Mango 80
10 Mango 100
3 Orange 30
7 Orange 70
I want to do this with the help of formula catering data on 1000s of rows. Kindly advice as soon as possible.
If you want to sort a table of numbers and text, where some of the numbers are not unique, but the combination of number and text is unique, you could try the following steps.
1. Create a vlookup table where each row has a number (col. 1) and letters of alphabet (col. 2). Make sure the 2nd col. has enough alpha chars to cover all your numbers. E.g., if you have 1 to 43 numbers, you'd need aa to bq in col. 2. Then (e.g.) store this in g1 to h43.
2. If your data is in A1 to B43 (say) put the following in C1 =vlookup(a1,$g$1:$h$43,2)&b1 and copy down to c43. In col. C, you'll now have entries like: "aaFred". This process concatenates two alpha chars to the text string to give a unique text string.
3. Then use chandos countif($c$1:$c$43,"<="&c1) to get each strings' exact alphabetical position in D1 & copy down.
4. Then in col. E use function like =RIGHT(c2,(LEN(c2)-2)) to strip off the first two chars.
5. Then in F col. use another vlookup(g1,$d$1:$e$43,2) to get the order you want.
Hope this helps in some way
In this grand design of things you actually receive an A with regard to effort. Exactly where you actually confused me was first in the details. You know, they say, the devil is in the details... And that couldn't be more true at this point. Having said that, permit me tell you just what did work. Your authoring is certainly highly convincing and this is most likely why I am making the effort in order to opine. I do not really make it a regular habit of doing that. Secondly, while I can easily notice a jumps in logic you make, I am definitely not convinced of how you appear to connect your points which in turn produce the final result. For right now I will, no doubt subscribe to your position however wish in the foreseeable future you link the dots much better.
I've prepared an example file which I hope you can download from the Dropbox address above.
Basically, the 'interface' tab lets you choose which criteria you want to be used in ranking people's (e.g.) sporting achievements. The 'data' tab holds the data for each criterion and the individual ranks. The 'calculations' tab is where the criteria selected are reproduced, summed and ranked. Then columns N to S take steps to re-arrange the results.
Column N adds a unique prefix (using 'Lookups'); Column O finds where column N would now appear in the revised order; Column P reproduces the people's names ready for the next Vlookup; Column Q now puts the names in the correct order; Column R gets the numbers in ascending order; and Column S checks for any equal values and adds the '=' sign to the front of the number if any are found.
Finally, Columns S and Q are reproduced in the 'Interface' tab.
Hope this helps
Simon
Maybe I'm being utterly dense on this thread; but i do need to understand the concepts from it. If I repeated a question that is here it is not intentional, it's really because I'm not understanding the process.
I've broken this down to smaller steps so that I understand this better at my end. I have 6 columns on my spreadsheet:
Column A: Index number (1 to 676)
Column B: Character strings Aa to Zz
Column C [skipped]
Column D: 101 Randomly selected character strings from column B
Column E: I believe I've identified the alphabetical ranking of Column C with the equation: COUNTIF($D$2:$D$102,"<="&D2)
Column F [skipped]
Column G: is another index number. This time between 1 to 101.
Column H: is supposed to be the Column D values in final alphabetization.
My instincts say I'm one step away from getting Column H. i've toyed with a number of equations for Column H from here and other web pages to reach this goal. For some reason the process is alluding me. Might someone here have to patience to walk me through this gap of knowledge that I'm not understanding?
Peter Kelley
St. Paul, MN USA
Thank you very much! This tip helps me a lot.
Hi,
that's very interesting issue.
is it possible to make the solution without a column of countif()? like sort the strings in one new column?
Hola to all of you
I am definitely too dump to understand how to use this formula. I will continue trying but may be someone of you can just switch on the light for me.
In Sheet B I do have a list of names in Cells A2:A24 and named them zHTs. That list I would want to be replicated in sorted order in Sheet A, cells A14:A36.
As in the beginning I dont have any reference value in my current sheet I thougt I can replace "<="&A6 with "<=ZZZ". So right now my formula looks like this:
=VLOOKUP(ROW()-13,COUNTIF(zHTs,"<=ZZZ")-1,1,FALSE)
--
Still Stunned
Mic
Coming back on this one more time. The formula to deliver in my scenario what I want is
=VLOOKUP(ROW()-13,zSortableHTsList,2,FALSE)
Nevertheless, I am still asking myself, if the aiding column at the data source cant be eliminated. Does that require as some of you guys mentioned an array formula?
--
Cheers Mic
Got it. I am still working on it but I am aware now it needs a separate column which Í didn't notice earlier.
Thank you very much
Michael
can i get video tutorial about this topic?
I've started using a query for sorting like this instead of a formula.
1. Make the list a table
2. The from the menu Data > From Table/Range (opens query)
3. From the drop-down next to the list, click sort
4. Click close & load from the query editor menu
You'll end up with something like this:
https://s22.postimg.cc/secjxu91t/Sort_by_name.png:
The reason I did it was to sort by last name, for which there are a couple of extra steps:
1. Make the list a table
2. The from the menu Data > From Table/Range (opens query)
3. Click Split columns > by delimiter
4. Select Space from the drop down, then OK
5. From the drop-down next to the last name column, click sort
6. Select both columns, then from the Transform menu select Merge columns, and choose Space as the Separator
7. Click close & load from the query editor menu, and you'll end up with a second table sorted by last name, and a query behind it, something like this:
https://s22.postimg.cc/vu4o6yj0h/sort_by_last_name.png
let
Source = Excel.CurrentWorkbook(){[Name="tbl_Names"]}[Content],
#"Sorted Rows" = Table.Sort(Source,{{"Names", Order.Ascending}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Names", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Names.1", "Names.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Names.1", type text}, {"Names.2", type text}}),
#"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Names.2", Order.Ascending}}),
#"Merged Columns" = Table.CombineColumns(#"Sorted Rows1",{"Names.2", "Names.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Of course, using this method you will have to manually refresh the list/table or use a macro/vba to do that automatically.
Thankyou for being inspired
Thank you, awesome website, glad been here
Nice Topics , this good for everyone anda very helpfull
can i get video tutorial about this topic?
Amazing.. Nice
Thank you for sharing
ah nice use of formule there
I am very happy and enjoy this website