tag:blogger.com,1999:blog-3366935554564939610.post4328254584684768008..comments2020-07-06T06:27:37.598-04:00Comments on Data Miners Blog: Sorting Cells in Excel Using Formulas, Part 2Michael J. A. Berryhttp://www.blogger.com/profile/06077102677195066016noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-3366935554564939610.post-54931259023769357582012-08-22T02:09:42.133-04:002012-08-22T02:09:42.133-04:00My solution is much simpler.
1. My unsorted numbe...My solution is much simpler.<br /><br />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<br /><br />2. My sorted numbers shall be in cells J29-O29.<br /><br />3. The formula for cell J29 is<br />=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))))))<br /><br />3. The formula for cell K29 is … just convert all the “=1″ into “=2″<br /><br />4. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.<br /><br />5. The RANK function will rank every cell in the range. There will not be any unranked. <br /><br />6. The last part .. ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29)<br />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<br /><br />7. To sort words, you need to first convert words into ASCII using the CODE function. The RANK function works only with numbers.<br /><br />Hope this would help u guys.<br />ArielMy bag of gemshttps://www.blogger.com/profile/16244387247943473140noreply@blogger.comtag:blogger.com,1999:blog-3366935554564939610.post-24793095062078104512012-08-22T02:04:10.940-04:002012-08-22T02:04:10.940-04:00My solution is much simpler.
1. My unsorted number...My solution is much simpler.<br />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<br />2. My sorted numbers shall be in cells J29-O29,<br />3. The formula for cell J29 is<br />=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))))))<br />3. The formula for cell K29 is … just convert all the “=1″ into “=2″<br />4. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.<br />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)<br />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.<br /><br />Hope this would help u guys.My bag of gemshttps://www.blogger.com/profile/16244387247943473140noreply@blogger.comtag:blogger.com,1999:blog-3366935554564939610.post-73656584593320199502012-08-22T02:03:36.844-04:002012-08-22T02:03:36.844-04:00My solution is much simpler.
1. My unsorted number...My solution is much simpler.<br />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<br />2. My sorted numbers shall be in cells J29-O29,<br />3. The formula for cell J29 is<br />=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))))))<br />3. The formula for cell K29 is … just convert all the “=1″ into “=2″<br />4. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.<br />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)<br />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.<br /><br />Hope this would help u guys.My bag of gemshttps://www.blogger.com/profile/16244387247943473140noreply@blogger.comtag:blogger.com,1999:blog-3366935554564939610.post-46554572690679388112011-12-10T09:24:06.292-05:002011-12-10T09:24:06.292-05:00In my testing, the formula above was incorrect for...In my testing, the formula above was incorrect for numeric items at least. It needed to be something like<br /><br />{=SUM(IF(data!B$2:B$55>=data!B2, 1, 0)) + 1 + SUM(IF(data!B$2:B2=data!B2, -1, 0))}<br /><br />to give a result where 1 was the first sorted entryJohn Alvordnoreply@blogger.comtag:blogger.com,1999:blog-3366935554564939610.post-84771972138188979852009-03-29T18:56:00.000-04:002009-03-29T18:56:00.000-04:00OK here is my next challenge. My dashboard returns...OK here is my next challenge. My dashboard returns Top 10 values for several KPIs. Each set of KPIs on the dashboard contains two columns: one for the state and one for the corresponding value. How do I return the correspoding state for each Top 10 value? Currently trying to use OFFSET function, but I'm either using bad syntax or using the wrong approach. Any help would be appreciated. <BR/><BR/>BTW, your blog is a data miner's dream. Thanks!Ian Leonghttp://visualconnect.comnoreply@blogger.comtag:blogger.com,1999:blog-3366935554564939610.post-23690783162285783182009-03-29T18:25:00.000-04:002009-03-29T18:25:00.000-04:00I second the previous comment. Ranking data from a...I second the previous comment. Ranking data from another tab with duplicate values is something I have been struggling with for a while. I am trying to create a dashboard with Top 10 values for several KPIs. Fortunately for me, my rows are also states so your Excel sheet with sample data was a perfect guide. Thanks again and I will be checking back regularly!<BR/><BR/>-IanIan Leonghttp://visualconnect.comnoreply@blogger.comtag:blogger.com,1999:blog-3366935554564939610.post-3268581617281584662008-12-23T09:39:00.000-05:002008-12-23T09:39:00.000-05:00Thanks for the post - this was very helpful soluti...Thanks for the post - this was very helpful solution. I had tried several methods with arrays, but kept running into issues with duplicates. This one is so simple, but works great!Anonymousnoreply@blogger.com