how to find top 25 Values from more than 1000 rows with their corresponding names

Hi all,

I needed help to find top 25 Values from more than 1000 rows with their corresponding names in a new sheet. I have a formula which works fine in a small data set but its not working with large data. the below is the formula which i got from a website and unfortunately i missed that link. I have a file also which i did a rough work. Maybe i need to change 10^5 value in the formula. can you please tell me what the meaning of 10^5 in this formula Any advice is greatly appreciated. Also can you please suggest how to use ISError function in this formula so that whenever #N/A ,#NUM! or something like this comes the cell shows as blank cell.

=INDEX($B$1:$B$20,MATCH(LARGE(INDEX($C$1:$C$20+(ROWS($C$1:$C$20)-ROW($C$1:$C$20))/10^5,0),ROWS($1:1)),INDEX($C$1:$C$20+(ROWS($C$1:$C$20)-ROW($C$1:$C$20))/10^5,0),0))

and to get corresponding amount :=LARGE($C$2:$C$21,ROWS($2:4))

Originally Posted by martindwilson

just use the unique rank you already have in column a and index match against that
see cols g:h

Thanks Martin for the Quick and very helpful reply .If possible i have one more issue i need to list all the entire 20 names in Coloumn G but if in the corresponding amount coloumn H " #NUM! " is coming then I need that cell to show as blank cell, but the formula should be there. Is there any work around for this

Thanks dave for the fast reply but when i use the first formula ots showin all alpha (Coloumn I sheet 3)

In the second formula the delta and charlie is displayed twice in the adjacent coloums (Sheet 2, E) eventhough their value is very different and also i am not able to populate the amount in the next coloumn "F" . :-)

Originally Posted by martindwilson

one way is to count how many in col a

Hi Martin,

Can i use iserror function in the formula you suggested "=INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,ROW(B1)),$A$2:$A$21,0),COLUMN(B1))"

=IF(ISERROR (INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,ROW(B1)),$A$2:$A$21,0),COLUMN(B1)," ",(INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,ROW(B1)),$A$2:$A$21,0),COLUMN(B1))

but when i tried its not working for me :-( Am i doing anything wrong?

Hi,

You can try the below formula.

=IFERROR(INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,ROW(B6)),$A$2:$A$21,0),COLUMN(B6)),"")


Thanks

Originally Posted by nawas

Hi,

You can try the below formula.

=IFERROR(INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,ROW(B6)),$A$2:$A$21,0),COLUMN(B6)),"")


Thanks

Hi Nawas,
Thanks but when i tried its not working on the sheet

Originally Posted by martindwilson

missing and incorrect () should be
=IF(ISERROR(INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,ROW(B1)),$A$2:$A$21,0),COLUMN(B1)))," ",INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,ROW(B1)),$A$2:$A$21,0),COLUMN(B1))

Thanks Martin, You are a superstar it works fine with the last formula
just need to add one more ) at the end . Thanks again