Ranking and Sorting Alphanumeric Data for Top 10 list

Hello there,

My 1st post here so I thank-you in advance for trying to help.

I'm trying for my cricket spreadsheet to find the Top 10 Not Out scores plus regular scores from same column along with the perople's names. Some of these cells have an "*" in the same cell,(representing a Not Out score), as the score so if a cell has the same number as a cell with an "*" then the cell with the "*" would be placed above it. I have a column A (names) and column B(alphanumeric data).

Example only (using 11 names)

COL A COL B

Bob 40
John 55*
Paul 40*
Hank 21
Chuck 35
Walt 75
Chris 55
Jabba 21
Tim 80
Stuart 36*
Roy 35*


Thanks!

ARRAY formula in F3

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(G3&"*"=$B$2:$B$12,ROW($B$2:$B$12),""),COUNTIF($G$3:$G3,$G3))-ROW($A$2)+1),"")

ARRAY formula in G3

=IFERROR(LARGE(IF(RIGHT($B$2:$B$12,1)="*",0+SUBSTITUTE($B$2:$B$12,"*",""),""),ROWS($G$3:$G3)),"")

ARRAY formula in J3

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(K3=$B$2:$B$12,ROW($B$2:$B$12),""),COUNTIF($K$3:$K3,$K3))-ROW($A$2)+1),"")

Formula in K3

=IFERROR(LARGE($B$2:$B$12,ROWS($K$3:$K3)),"")

Copy down all.

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

I read this differently!!

i assumed that ONE list was required, with Not Outs appearing above the Outs. Easily done with a helper column (which can be hidden).

helper:
=IF(ISNUMBER(SEARCH("~*",B3)),SUBSTITUTE(B3,"*","")+0+ROW()/100000000,SUBSTITUTE(B3,"*","")+0)+ROW()/10000000000
Then this, copied across and down.
=INDEX(A$3:A$13,MATCH(LARGE($C$3:$C$13,ROWS($1:1)),$C$3:$C$13,0))

Or (minus helper column), an array formula:

Formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

With data in A2:B12

Please try at
D2:E12
=INDEX(A:A,MOD(SMALL(INDEX(ROW($B$2:$B$12)-SUBSTITUTE($B$2:$B$12,"*",".1")*10000,),ROWS(D$2:D2)),1000))

post deleted.

Post deleted.