Excel Formula Help

Hello!

I'm not sure if this is the right thread but since it involves a bit of programming using excel formula I decided to put it here on this thread.

Ok i have this table, Let's say column B is score



What I want is, I want to show the top 10 scorer which will display the name and score beside the name. Something like this:



You can see the top 10 on the above image.


Anyone know how can I do this? and what formula?

An example would be highly appreciated. Anyone?

Thanks!

 

Hello,

It is very simple. First click on any cell of column B, then click on "Sort & Filter" tab in Excel 2007. It will show a drop down menu and in it Click on "Custom Sort...". And in it click on " Largest to smallest" in order tab and then click OK and it should be done as you want.

Hope it helps.

Thanks,
Serious Workers

 

Thanks for that, But the problem is I want to show the top 10 in different columns and I don't want to mess up column A and B. That's why you'll noticed on the 2nd pic that I put it on column D and E.

Any idea how can I do that?

 

I don't think there is any automated formula to do that until I know. But, I will check it and reply to you if I find any such things.

 

Ok thanks

Any other suggestions on how to solve this? Thanks

 

I think the easiest thing is to create a pivot table on the same sheet.

 

any links on how can I do that? thanks

 

pivot table on the same sheet is the simplest thing in this case. just google it.

 

Pivot seems ok, but that is not exactly what I want.

I don't want to refresh the pivot table once I am changing a value on column B.

What I want is, the top 10 will also automatically change whenever I am changing the the value in column B

 

To column E1 add formula =LARGE(B:B;1) to column E2 add =LARGE(B:B;2) .... E10 add formula =LARGE(B:B;10)

To column D1 add formula =IF(N(E1);INDEX(A:A;LARGE(IF(B:B=E1;ROW(A:A)-ROW($A$1)+1);COUNTIF($E$1:E1;E1)));"") and enter with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
To column D2 add formula =IF(N(E2);INDEX(A:A;LARGE(IF(B:B=E2;ROW(A:A)-ROW($A$1)+1);COUNTIF($E$1:E2;E2)));"") and enter with Ctrl+Shift+Enter
...........
To column D10 add formula =IF(N(E10);INDEX(A:A;LARGE(IF(B:B=E10;ROW(A:A)-ROW($A$1)+1);COUNTIF($E$1:E10;E10)));"") and enter with Ctrl+Shift+Enter

 

how about the file I sent to you. I sending PM for you and no answer... What happens?

 

To witch user you are sending PM?

 

It's for irule272. I sent a excel file with the requirements. Before, he keeping talk to me. When I sent file, I didn't got answers anymore... I want to know what happened!

 

Dude you didn't get any reply because what you sent to me is not what I needed or didn't exactly what I need and also someone's already gave me the correct formula before you sent your last e-mail to me. Sorry if I didn't notify you right away.