ubervu
Apple iPhone
Nexus One
+ Add
EMAIL ALERTS: SUBSCRIBE
By: David
Updated 1 month, 2 weeks ago
Source: http://chandoo.org/
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
- 15 comments on this story
Showing 13 relevant reactions out of 15.
[...] 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 [...]
Sorting text using formulas http://bit.ly/2j6izs [PHD time-machine]
@lohhw3 oops..my mistake, I think you need to use formulas to sort numbers and text. learn more: http://bit.ly/q1zw9
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
@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.
@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…
...
See all content
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!
@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
...
See all content
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!
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.
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
...
See all content
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.
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″
Follow us
FEEDBACKTwitterFacebook-
DASHBOARD
PRO
- MY KEYWORDS
PRO
-
TOOLS & WIDGETS