Top 20 list from multiple sheets

[Horatio J. Bilge, Jr.]

I'm not sure how to solve this problem, so hopefully someone can help me with
a solution... I have several sheets that contain swimmers' times, and I want
to come up with a Top 20 list for each event. Each year I will add new data,
and I want the Top 20 to update.

The data looks like this (a different sheet for each year), with 20-40 rows
of data. Columns C and D have data that's not important for the Top20. The
list continues to the right in the same manner to include 7 events.
A B C D E ... Y
Name 200 Free 200 IM Year
Jane Doe 2:15.1 2:19.3 2008
Sue Smith 2:11.6 2:39.9 2008
Ann Johnson 2:06.4 2:31.6 2008

The result that I want to achieve would pull the top 20 from all of the
sheets, and would look like this (a different list for each event):
200 Free, Top 20
Ann Johnson 2:06.4 2008
Sue Smith 2:11.6 2008
Jenny Johnson 2:12.0 2006
etc.

I have tried a few solutions, but I'm stuck on all of them:
1. I tried creating a PivotTable
Problem: I can't use multiple sheets and still manipulate the data
meaningfully
2. I tried merging all of the sheets into one sheet, and making a PivotTable
of that sheet
Problem: It's labor intensive to merge the sheets
Problem: After creating the PivotTable, it rounds the values (e.g.,
2:06.8 becomes 2:07.0)
Problem: When I sort the PivotTable for the Top20 (bottom 20,
actually), it includes empty cells as a top time when a swimmer does not have
a time for an event
3. I tried importing the data into an Access database
Problem: After importing the first sheet, I couldn't import the other
sheets into the same table
Problem: I've never really used Access, so I'm not sure what I'm doing

 

[BobT]

The best answer is Access, but due to your lack of experience with it, we'll
make it work with what you have.

I would suggest creating top 20 list per sheet (where, if I understand
correctly, each sheet is a different year). Once a year is over, that sheet
won't be updated again (at least I don't see why it would). Create a "top 20
of all time" sheet where these top 20 lists/year go. Then all you have to do
for your "top 20 ever" list is reference the other top 20/year lists.

I'm assuming you know how to get the top 20/year, so I won't go into those
steps.

 

[Horatio J. Bilge, Jr.]

I like your suggestion (at least until I get a chance to play around with
Access more). I thought it would be easy to make the Top20 list for each
year, but I've run into a snag.

I used this formula to rank the Top20 times for each event (adapted from
Chip Pearson's site):
=IF(ROW()-ROW(Top20)+1>TopN,"",SMALL(RankEvent,ROW()-ROW(Top20)+1))
** RankEvent is the range that includes all of the times (B2:B30).
** Top20 is the range that the Top20 is listed in (Z2:Z30).
** TopN is a named cell that I can change - currently it's value is 20, for
the Top20.
That worked great.

Next, I needed to get the names that correspond to each time. In some cases
there are two swimmers with the same time, so I need to take that into
account. I found a formula at
http://office.microsoft.com/en-us/excel/HA012260381033.aspx for returning
multiple corresponding values. But to tweak it for my purposes, I get a #NUM
error. Instead of referring to a separate cell like they do in their example,
I am referring to an entry in the Top20 list. The array formula (entered in
AA2 and copied down for all cells) is:
=INDEX(Data2008,SMALL(IF(RankEvent=$Z2,ROW(RankEvent)),ROW(1:1)),1)

The first cell gives the expected swimmer's name as a result, but all of the
other cells give the #NUM error.