Hello Friends
Please find the attached file (sort as per most frequency 030116.xlsx)
I want to sort / rank the alphabets as per the count in light yellow cells by formula.
thanks in advance
thilag
Hello Friends
Please find the attached file (sort as per most frequency 030116.xlsx)
I want to sort / rank the alphabets as per the count in light yellow cells by formula.
thanks in advance
thilag
Hi thilag,
If I understand you correctly, the easiest thing to do is to create a "helper column" in Col B with the same formula you have in E3. Than copy it down.
Now sort Col A and B in descending order based on Col B.
I hope this helps, please let me know!
Regards,
David
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".
Hi thilag, by amending beaunydal's formula's fromto suit and using your existing columns D&E as helper columns, see if this works for you.HTML Code:
use pivot table with sort feature
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hello Bearmernsw and David
thanks for your posts. Bearmernsw the file uploaded by you works fine. Also i need to use the following non array formula in D2 <=IFERROR(INDEX($A$1:$A$100,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$1:$A$100),),)),"")> and drag down then every manual inputs can be removed.
thanks again
Let us wait for the solution without helper formulas.
thilag
Hi there, Thilag. try this:
Regular formula in D2:
=INDEX($A$1:$A$100,MODE(MATCH($A$1:$A$100,$A$1:$A$100,0)))
Array formula in D3, copied down:
=IFERROR(INDEX($A$1:$A$100,MODE(IF(COUNTIF($D$2:D2,$A$1:$A$100)=0,MATCH($A$1:$A$100,$A$1:$A$100,0)+{0,0}))),"")
Regular formula in E2:
=COUNTIF(A:A,D2)
See file...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Another version from Glenn's :
On D2:
=IFERROR(INDEX($A$1:$A$100,MODE(IF(($A$1:$A$100<>"")*1*ISNA(MATCH($A$1:$A$100, D$1:D1,0)),MATCH($A$1:$A$100,$A$1:$A$100,0)*{1\1}))), "")
array formula entered and copied down as necessary
cheers
The second formula is about as convoluted as they get. It's the only way I could find to sort the A1:A100 range-in-formula (before I saw azumi's remarkable formula). I'm posting anyway.
In array-entered in E2 to get the counts.
=LARGE(COUNTIF($A$1:$A$100,IF(FREQUENCY(MATCH($A$1:$A$100,$A$1:$A$100,0),ROW($1:$100)),$A$1:$A$100)),ROWS($1:1))
then this 'goblin' array-entered in D2.
=INDEX(INDEX($A$1:$A$100,N(IF(1,MATCH(SMALL(COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),ROW($1:$100)),COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),0)))),SMALL(IF(IF(FREQUENCY(MATCH(INDEX($A$1:$A$100,N(IF(1,MATCH(SMALL(COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),ROW($1:$100)),COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),0)))),INDEX($A$1:$A$100,N(IF(1,MATCH(SMALL(COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),ROW($1:$100)),COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),0)))),0),ROW($1:$100)),COUNTIF($A$1:$A$100,INDEX($A$1:$A$100,N(IF(1,MATCH(SMALL(COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),ROW($1:$100)),COUNTIF($A$1:$A$100,"<="&$A$1:$A$100),0))))))=E2,ROW($1:$100)),COUNTIF($E$2:E2,E2)))
Edit If column A is sorted this is much easier on the eyes.
=INDEX($A$1:$A$100,SMALL(IF(IF(FREQUENCY(MATCH($A$1:$A$100,$A$1:$A$100,0),ROW($1:$100)),COUNTIF($A$1:$A$100,$A$1:$A$100))=$E2,ROW($1:$100)),COUNTIF($E$2:$E2,$E2)))
Last edited by FlameRetired; 01-03-2016 at 03:59 PM.
Dave
Azumi. Looks really good. That formula is definitely a "keeper". Just one thing, should the \ not be a , ????
Hello Azumi
thanks for the formula
<=IFERROR(INDEX($A$1:$A$100,MODE(IF(($A$1:$A$100<>"")*1*ISNA(MATCH($A$1:$A$100, D$1:D1,0)),MATCH($A$1:$A$100,$A$1:$A$100,0)*{1\1}))), "")>
but the attached file shows the error message when using your formula. I thing there may be some error in 1/1 area.
Other formauls workng well thanks.
thilag.
Thanks to all of the friends who have helped me.
thanks again
thilag
thilag,
An afterthought:
Just in case you also want the letters alpha-sorted within their common count groups (which is what the original upload seems to indicate)
without helper column this is much shorter than my previous attempt.
For the counts array-enter this in E2 first and fill downthen array-enter this in D2 and fill down.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks