Results 1 to 2 of 2

Thread: Formula to select first 3 runners and first in each category

  1. #1

    Formula to select first 3 runners and first in each category



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi
    I'm doing some road race results and would like to set up a formula on another sheet looking up the first 3 finishes, and then the first runner from each category. So I want to be able to have on another sheet looking up the results:

    So below is an example of the race results and I would like on another sheet listing the top 3 male runners, 3 female runners and then 1st M40, M45, M50 etc etc. But ( looking at the list under the results) if a F40 comes 3rd I need her name in the top 3 finishes for female and also the 1st F40. I've tried and tried but just can't get it right.
    Can anyone help please

    No Forename Surname Cat Club Time Pos
    157 Marc Hartley M Stoke AC 38.4 1
    123 Jimmy Craig M Barlick Fellrunners 39.09 2
    52 Nick Treitl M Barlick Fellrunners 40.3 3
    118 Robert James M U/A 41.02 4
    105 Michael Keedy M40 Skipton AC 41.27 5
    101 Andy Buttery M40 Blackburn AC 41.45 6
    70 Stephen Stockwell M45 West Australia 41.56 7
    143 Thomas Corrigan M U/A 42.03 8
    18 John Lloyd M Salford Harriers 42.43 9
    1 Thomas Brewster M Clayton le Moors Harriers 43.31 10
    146 Gary Pearse M45 Blackburn AC 43.56 11
    131 Kevin Davies M50 Clayton Harriers 44.14 12
    69 Nicholas Cook M Trawden AC 44.16 13
    102 Sean Molloy M45 Rossendale 44.21 14
    36 Andy Berry M Barlick Fellrunners 44.24 15
    39 Chris Quigley M45 Trawden AC 44.27 16
    156 Andrew Driver M Barlick Fellrunners 44.38 17
    111 Paul Hesketh M45 Clayton Harriers 44.43 18
    119 Paul Redman M40 Trawden AC 44.45 19
    83 Thornton Talyor M55 Rossendale 44.57 20

    No Forename Surname Cat Club Time Pos
    1st Male 157 Marc Hartley M Stoke AC 38.4 1
    2nd Male 123 Jimmy Craig M Barlick Fellrunners 39.09 2
    3rd Male 52 Nick Treitl M Barlick Fellrunners 40.3 3
    1st M40
    1st M45
    1st M50
    1st Female 66 Sarah Tipler F Barlick Fellrunners 47.05 31
    2nd Female 17 Autumn Howard F Trawden AC 49.57 54
    3rd Female 38 Lorraine Slater F40 Barlick Fellrunners 51.54 67
    1st F40 38 Lorraine Slater F40 Barlick Fellrunners 51.54 67
    1st F45
    1st F50

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Barryb View Post
    So below is an example....
    You should set an example of your file, then it could be easier to help you.
    You try my idea
    I would have tried to solve the following way

    On Sheet1 I have added four extra columns, these columns you can hide to be invisible

    On Sheet2 I put the following formulas

    The first category of runners
    In cell B2 array CSE formula
    Code:
    =INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L2=Sheet1!$D$2:$D$21)*(ROW(Sheet1!A1)=Sheet1!$J$2:$J$21);0))
    In cell C2, this formula you copy to the last column to the right and down for all categories
    Code:
    =VLOOKUP($B2;Sheet1!$A$2:$G$21;COLUMN(Sheet1!B$1);FALSE)
    The second category of runners
    In cell B6 array CSE formula
    Code:
    =INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L6=Sheet1!$D$2:$D$21)*(ROW(Sheet1!$A$1)=Sheet1!$K$2:$K$21);0))
    The third category runners
    In cell B11 array CSE formula
    Code:
    =INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L11=Sheet1!$I$2:$I$21)*(ROW(Sheet1!A2)=Sheet1!$J$2:$J$21);0))
    The fourth category runners
    In cell B15 array CSE formula
    Code:
    =INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L15=Sheet1!$D$2:$D$21)*(ROW(Sheet1!$A$1)=Sheet1!$K$2:$K$21);0))
    To resolve the errors you use IFERROR or IF/ISERROR
    I hope I helped you, if this is not the solution I gave you an idea

    Pay attention to the separator argument inside a formula. You may need to use a comma (,) instead of a semicolon (;)
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	rank.png 
Views:	5 
Size:	28.9 KB 
ID:	3682  
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •