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.
|
Nou pe simpatie:
Kalifa
 | Femeie
24 ani
Ialomita
cauta Barbat
24 - 65 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #1 |
TRaPModerator
Postari: 960
|
|
Source:
https://www.excelforum.com/excel-formul ... n-end.html
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)))
|
|
| |
|
| #2 |
|
|
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));"")
_______________________________________
|
|
| |
|
