Need to filter a table by using formulas, not Sort or AutoFilter or VBA

Can't you insert an new column A and enter the
formula, =RANK(C2,range), into cell A2, Copy down,
and then use VLOOKUP() to obtain the values for
your new table?

I was playing around with index match and the large function, but I think both these will face a challenge when the same test score or rank exist. I think in either case, only the first name will be returned. I'm not real sure how to overcome this.

Duplicates are a problem

You're right about when the same score occurs more than once. The Rank and Vlookup works, except it does only return the first occurrence of the score. It just ignores the second occurrence and goes on down the list to the next unique ranking. Well, at least we're in the ballpark now. Thanks to both of you for your suggestions.
Connie

I just helped someone with the same problem, Here is the answer:

Use the LARGE function; =LARGE(Range, place from top). Range can also be an Array.

If your scores are in B1:P1 and you put this formula in cells Q1:AE1 (=LARGE(B1:P1,x)
where x = 1 to 15 you will get the top 15 scores with the highest in Q1 and lowest 15th in AE1.

Then in cell AF1, SUM the 15 scores.

You may have a problem if you can't use a sort. Try Data-Sort-Ascending on the menu for the Names, by AF1 the SUM, this will give you the finish order of all data. Vlookup may help to sort by the score? JSW

I just helped someone with the same problem, Here is the answer:

Use the LARGE function; =LARGE(Range, place from top). Range can also be an Array.

If your scores are in B1:P1 and you put this formula in cells Q1:AE1 (=LARGE(B1:P1,x)
where x = 1 to 15 you will get the top 15 scores with the highest in Q1 and lowest 15th in AE1.

Then in cell AF1, SUM the 15 scores.

You may have a problem if you can't use a sort. Try Data-Sort-Ascending on the menu for the Names, by AF1 the SUM, this will give you the finish order of all data. Vlookup may help to sort by the score? JSW

You can also run the LARGE function on the total score to sort! Just use the column range for the SUM of the top 15 scores with the LARGE function in an additional table. I just helped someone with the same problem, Here is the answer: Use the LARGE function; =LARGE(Range, place from top). Range can also be an Array. If your scores are in B1:P1 and you put this formula in cells Q1:AE1 (=LARGE(B1:P1,x)

Band-Aid

Another route would be as follows:
I'm assuming your unsorted scores are on sheet 1 in columns a and b with a header (through row 18)
On your summary sheet, list the scores in column B with:
=LARGE(Sheet1!$B$2:$B$18,ROW()-1) (starting in row 2)and copy down to row 16
Now in column A, use the following
=IF(COUNTIF(Sheet1!$B$2:$B$18,B2)=1,INDEX(Sheet1!$A$2:$B$18,MATCH(B2,Sheet1!$B$2:$B$18),1),"PUT NAME HERE") and copy down to row 16
This should get you the names for all unique scores and a lovely "Put Name Here" for duplicates.

Good luck until some one solves this one.

Connie

I remember being involved in replying to similar questions in the past. However, Celia [a regular contributor here a while ago] has taken up a question before I could, which is, in my judgment, identical to yours. Here is her solution:

I'll assume the following data that occupy the range A1:B10 as example.

{"Names","Scores";"a1",23;"a2",21;"a3",24;"a4",24;"a5",17;"a6",19;"a7",2;"a8",28;"a9",31}

In C2 array-enter: RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1 [ copy down as far as needed ]

In E2 enter: =IF(ROW()-1<=5,INDEX(A$2:A$10,MATCH(ROW()-1,$C$2:$C$10,0)),"") [ copy this to F2 then down up to last row of data in A ]

You'll get your new table in E2:F6 which is:

{"a9",31;"a8",28;"a3",24;"a4",24;"a1",23}

Note 1. The number 5 in the condition part of the IF-function must be changed in your case to 15.

Note 2. The new table is on the same sheet as the data. It can also be produced on a different sheet very easily.

Aladin

==================

This works perfectly on my test data. Very cool (nt)

I remember being involved in replying to similar questions in the past. However, Celia [a regular contributor here a while ago] has taken up a question before I could, which is, in my judgment, identical to yours. Here is her solution: I'll assume the following data that occupy the range A1:B10 as example. {"Names","Scores";"a1",23;"a2",21;"a3",24;"a4",24;"a5",17;"a6",19;"a7",2;"a8",28;"a9",31} In C2 array-enter: RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1 [ copy down as far as needed ] In E2 enter: =IF(ROW()-1<=5,INDEX(A$2:A$10,MATCH(ROW()-1,$C$2:$C$10,0)),"") [ copy this to F2 then down up to last row of data in A ] You'll get your new table in E2:F6 which is: {"a9",31;"a8",28;"a3",24;"a4",24;"a1",23} Note 1. The number 5 in the condition part of the IF-function must be changed in your case to 15. Note 2. The new table is on the same sheet as the data. It can also be produced on a different sheet very easily. Aladin ==================

Excellent solutions!! Thanks to all of you who contributed!

Wow, what great solutions! Thanks to all of you for your super ideas! Hopefully one day I'll have enough Excel knowledge to be able to help others like you all do. I really appreciate your willingness to share your skill and knowledge with others.
Thanks again,
Connie

A note

Connie -- Theere is no need to enter the formula in C2 as an array-formula, a fact that makes the system more attractive.

Aladin

============