Sort / Rank as per maximum to minimum count

[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

[David A Coop]

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".

[Beamernsw]

Hi thilag, by amending beaunydal's formula's from

to suit and using your existing columns D&E as helper columns, see if this works for you.

[samba_ravi]

use pivot table with sort feature

[thilag]

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

[Glenn Kennedy]

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...

[azumi]

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

[FlameRetired]

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)))

[Glenn Kennedy]

Azumi. Looks really good. That formula is definitely a "keeper". Just one thing, should the \ not be a , ????

[thilag]

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.

[azumi]

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.

yes sorry about the error, maybe coz my indonesian system, just check the file

[thilag]

Thanks to all of the friends who have helped me.

thanks again

thilag

[FlameRetired]

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 down

Please Login or Register  to view this content.

then array-enter this in D2 and fill down.

Please Login or Register  to view this content.