Re: Top 10 Text Values

Domenic wrote...
>Assuming that A2:A100 contains your text values, try the following which
>will take into consideration ties for 10th place...
>
>B2, copied down:
>
>=IF((A2<>"")*(ISNA(MATCH(A2,$A$1:A1,0))),COUNTIF($A$2:$A$100,A2),"")
>
>C2, copied down:
>
>=IF(N(B2),RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1,"")
>
>D1: 10
>
>This indicates that you want a Top 10 list. You can change this as
>necessary. For example, if you want a Top 5 list, enter 5 instead.
>
>E1:
>=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(D1,C2:C100,0)),C2:C100))-D1
>
>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
>F2, copied down:
>=IF(ROW()-ROW($F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$100,
>MATCH(ROW()-ROW($F$2)+1,$C$2:$C$100,0)),"")
....

Ancillary cells aren't necessary.

B2 [array formula]:
=INDEX($A$2:$A$100,MODE(MATCH($A$2:$A$100,$A$2:$A$100,0)))

B3 [array formula]:
=INDEX($A$2:$A$100,MODE(IF(COUNTIF(B$2:B2,$A$2:$A$100)=0,
MATCH($A$2:$A$100,$A$2:$A$100,0))))

Fill B3 down into B4:B11.

Ancillary cells can make this more efficient, but only additional one
column suffices.

B2:
=COUNTIF(A2:A100,A2)+1

B3:
=IF(COUNTIF(A$2:A2,A3)=0,COUNTIF(A3:A$100,A3)
+ROWS(B3:B$100)/ROWS($A$2:$A$100))

Fill B3 down into B4:B100.

C2:
=INDEX($A$2:$A$100,MATCH(MAX($B$2:$B$100),$B$2:$B$100,0))

C3 [array formula]:
=INDEX($A$2:$A$100,MATCH(MAX(IF($B$2:$B$100<VLOOKUP(C2,$A$2:$B$100,2,0),
$B$2:$B$100)),$B$2:$B$100,0))

Fill C3 down into C4:C11.

.

Relevant Pages

  • Re: Top 10 Text Values
    ... >>>1) It doesn't take into consideration ties for 10th place. ... those rows in the COUNTIF call unless your goal is an INEFFICIENT ... Yours [array formula]: ... Mine is probably a bit slower in calculation speed, ...
    (microsoft.public.excel.worksheet.functions)
  • 2 Column Data lookup
    ... Is there a better way of accomplishing the above (an array formula or ... Hari ... India ... Prev by Date: ...
    (microsoft.public.excel.misc)
  • Re: Index and match functions help needed.
    ... Resolved via an email exchange .. ... The array formula was correctly entered and it correctly returned #N/A due ... Prev by Date: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: OFFSET??
    ... > Use the array formula (entered using Ctrtl-Shift-Enter) ... >> I've tried with combinations of MATCH and OFFSETR as well with no luck. ... Prev by Date: ...
    (microsoft.public.excel)
  • Re: minimum along rows
    ... > The simplest way to handle your example data (or any 2-column ... > comparison) would be to use an array formula like ... Bruno ... Prev by Date: ...
    (microsoft.public.excel.worksheet.functions)
Loading