+ Reply to Thread
Results 1 to 5 of 5

Need Help Finding Tournament Winners

  1. #1
    Registered User
    Join Date
    05-03-2022
    Location
    Florida, US
    MS-Off Ver
    Office 365
    Posts
    2

    Question Need Help Finding Tournament Winners

    Hello and thanks so much for your time!

    I'm about to be a judge in a fishing tournament for the first time, and the online system the organization uses is designed to rank winners based on simple things like length and weight. However, this tournament determines its winners...differently.

    - First, Second, and Third place winners go to the people who catch at least 3 different species of fish (from five eligible species) and will be ranked on the total inches of the longest fish from each eligible category.
    - The Most Valuable Angler goes to the person who catches at least 4 different species of fish (from five eligible species) and will be ranked on the total inches of the longest fish from each eligible category.
    - The Top Team will go to the team who catches the greatest number of inches of eligible fish. (Edit: I think I've figured this one out already!)

    As a result, it took them FOREVER to figure out who won. To avoid the same fate this year, I am trying to figure out how to automatically determine and show the winners. Luckily, I have last year's spreadsheet and tried to use pivot tables, but am not having much success.

    Any and all help is much appreciated and I really am grateful to you for your time!
    -JS
    Attached Files Attached Files
    Last edited by jsantsr; 05-05-2022 at 09:59 AM.

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,428

    Re: Need Help Finding Tournament Winners

    3 tables created:
    (1) 1st table - summary showing max length for each Fish Family by Angler
    (2) 2nd table - showing ranking for Winners
    (3) 3rd table - showing ranking for Most Valuable Angler

    Formula in B3=UNIQUE(Logs!D2:E365), in D3=TRANSPOSE(UNIQUE(Logs!G2:G365,FALSE)),
    in AD3=INDEX($B$3:$B$89,AGGREGATE(15,6,ROW($B$3:$B$89)-ROW($B$2)/($X$3:$X$89=AF3),COUNTIF(AF$3:AF3,AF3))) etc etc.

    Please see attached file.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,842

    Re: Need Help Finding Tournament Winners

    I created new sheets in the attached. The first sheet gets the 1,2, and 3rd place winners and also the Master Angler. The other sheet gets the Team winner.

    A clarification - in your description, it says you add up the total inches of the LONGEST fish in each category. It looks like you were adding up ALL the inches of all fish caught in each category. Mine solution does the prior. Hopefully I interpreted it correctly.

    For the 1,2,and 3 place, and the Master Angler, my first column first gets all the unique names and sorts them alphabetically:
    =SORT(UNIQUE(Logs!D2:D365),,1)

    Then for each of the five families, it shows the longest fish caught in each category:
    =MAXIFS(Logs!$I$2:$I$365,Logs!$D$2:$D$365,$A5#,Logs!$G$2:$G$365,B$4)

    Then I sum them up IF they caught at least 3 categories:
    =IF(COUNTIFS($B5:$F5,">0")>=3,SUM($B5:$F5),0)

    Then they are ranked:
    =RANK.EQ(G5,$G$5:$G$91)

    Similar to Master Angler, but I just make sure they caught at least 4 categories.

    I do very similar for the Team winner. 1st I show the unique team names:
    =UNIQUE(Logs!E2:E365)
    Then, instead of MAXIFS, I use SUMIFS to get all the inches caught:
    =SUMIFS(Logs!$I$2:$I$365,Logs!$E$2:$E$365,$A5#,Logs!$G$2:$G$365,B$4)
    I then total them up and rank them.

    Please see attached
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,428

    Re: Need Help Finding Tournament Winners

    Noted on the 5 eligible Fish Families, I have removed all the "? "and " ?" from Logs sheet.

    Attached amended workbook.

  5. #5
    Registered User
    Join Date
    05-03-2022
    Location
    Florida, US
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Need Help Finding Tournament Winners

    Thank you all for your help! You helped save us HOURS!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to display tie winners?
    By TXT007 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-17-2019, 10:02 PM
  2. [SOLVED] picking out two winners
    By Tresfjording in forum Excel Formulas & Functions
    Replies: 28
    Last Post: 09-02-2018, 09:19 AM
  3. [SOLVED] Ranking and finding the lone skin for my golf tournament spreedsheet
    By usbrunsbr in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-20-2015, 10:08 PM
  4. Help - Count Top 10 Winners
    By tyleromaha in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 08-09-2012, 02:21 PM
  5. no duplicate winners
    By gricardo in forum Excel General
    Replies: 1
    Last Post: 02-22-2009, 03:12 PM
  6. Highlight winners in a list.
    By samtwilliams in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2007, 10:52 AM
  7. listing winners formula
    By sirdef in forum Excel General
    Replies: 4
    Last Post: 06-05-2006, 12:26 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1