Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff. 		Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat. 		Nou pe simpatie:
mariaa5 pe Simpatie
Femeie
24 ani
Bucuresti
cauta Barbat
31 - 48 ani
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Find 1st, 2nd, 3rd etc. Most Frequent String in Range Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 811
Source:

Array formula:
=INDEX(Data;MODE(IF((Data<>"")*ISNA(MATCH(Data;$C$1:$C6;0));MATCH(Data;Data;0) * {1\1})))

Where Data is a named range which refers to:
=Sheet4!$A$1:INDEX(Sheet4!$A:$A; LOOKUP("zzz"; Sheet4!$A:$A; ROW(Sheet4!$A:$A)))
pus acum 5 ani
   
Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2251
With use of a helper column for ranking

Helper column, assuming column S is the column for which ranking is made:
=IF(S6="";"";IF(COUNTIF(S$6:S6;S6)=COUNTIF($S$6:$S$100;S6);COUNTIF($S$6:$S$100;S6)+((1/ROW())/10);""))

The ranked strings from column S are obtained with this formula (dragged until it shows blank cells):
=IFERROR(INDEX($S$6:$S$19;MATCH(LARGE($T$6:$T$19;ROWS($1:1));$T$6:$T$19;0));"")

I'm using the helper column inside a table, so while the helper formula must remain the same even inside the table, the formula above could be:
=IFERROR(INDEX(Table2[Jud];MATCH(LARGE(Table2[Rnk];ROWS($1:1));Table2[Rnk];0));"")

_______________________________________
pus acum 5 ani
   
Pagini: 1  

Mergi la