+ Reply to Thread
Results 1 to 9 of 9

Help with a Racing Leaderboard

  1. #1
    Registered User
    Join Date
    11-18-2020
    Location
    uk
    MS-Off Ver
    excel
    Posts
    5

    Help with a Racing Leaderboard

    Hi everybody!

    I am new to the group and have a strange obsession with creating spreadsheets. call me mad!

    I was wondering if anyone can point me in the right direction. What I want to be able to do is take a race results from a website, this will already have everyone in the correct position for the race, I would want to copy and paste that into a spreadsheet. From here I would like the results populate a leaderboard so for example "Roger" would be 1st, he would get 10 points and Race 1 would show "Roger" with 10 points on the leaderboard. 1st race is easy I can do that. The next race I would need to do the same thing and "Roger may place 4th so would get 6 points, I would then need the leaderboard to show Roger with 16 points after race 2. I believe I could do this also. The issue I have is that the racers change on a weekly basis. So I dont have say 20 Racers which will be the same each week. I could have 50 racers but only the top 10 would be receiving points. Would it be possible to automate this so add points for each individual racer?

    Thanks for your help

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Help with a Racing Leaderboard

    Welcome to the forum.

    Yes, what you have asked is perfectly possible. If you want any more specific detail, provide a sample workbook with a mock-up of what you envisage. I imagine that a combination of SUMIF and RANK will do what you want, but I'll wait to see your mock-up. There are instructions at the top of the page explaining how to attach your sample workbook.

    Please update your forum profile to show which version of Excel you are using. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    11-18-2020
    Location
    uk
    MS-Off Ver
    excel
    Posts
    5

    Re: Help with a Racing Leaderboard

    Hey Ali G

    Thanks for your response, I havent replied as I wanted to make sure I have the majority of the sheet done and know what I want to do.

    I have attached the workbook.

    1st issue is I have to have a raw data sheet to paste the information from a website, you can see how it pastes into excel, i cannot export from the website, so I have sorted this data using the copy cell forumla but this messes up all my other formulas which were working perfectly before I did this. Any help would be much appreciated.

    The copied file is the one i have edited so i can copy the raw data over, before I had to manually enter which is time consuming
    Last edited by freddiehick; 12-08-2020 at 11:15 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Help with a Racing Leaderboard

    It seems that you want a formula that will copy the data in columns D:L from the Raw Data sheet to the Race Results Sorted sheet. If that is the case perhaps the following will help.
    1. Insert a row 1 to both sheets.
    2. Populate A2 and down on both sheets using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3. Populate E3:M34 on the Race Results Sorted sheet using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    11-18-2020
    Location
    uk
    MS-Off Ver
    excel
    Posts
    5

    Re: Help with a Racing Leaderboard

    Thanks for this JeteMC, I have applied the formulas you have suggested but seem to get some errors after Race 2 I have attached a spreadsheet, it seems to do the same thing on the spreadsheet you attached, also if you check 'Results and Overalls sheet, when I have a null valve in the Names column it the sheets puts a 0 in which then makes my VLOOKUP pick up the ) from the previous unpopulated sheets and shows some race results for the ) value as 10 which messes up my rankings.

    Thanks for your help really appreciate it. Im nearly there just struggling to get the last bits to come together
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-18-2020
    Location
    uk
    MS-Off Ver
    excel
    Posts
    5

    Re: Help with a Racing Leaderboard

    Had a little play and I was missing data in the B column on 'Results Sorted however I am still getting errors further down
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Help with a Racing Leaderboard

    I think that the following may clear up a few of the issues.
    1. Modify the the formula on the Race Results Sorted sheet to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. On the Leaderboards sheet modify the Position formula to read: =IF(F3="","",RANK(F3,F$3:F$12,0))
    3. Modify the formula in column D to read: =IF(C3="","",10-ROWS(A$1:A1)+1)
    4. Modify the Total Points formula to read: =IF(D3="","",D3+E3)
    There also may be an issue with duplicate names on the Results and Overall sheet which may be more easily remedied by putting all of the data in the Leaderboards in columns B:I much as the data in the Race Results Sorted sheet.
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    11-18-2020
    Location
    uk
    MS-Off Ver
    excel
    Posts
    5

    Re: Help with a Racing Leaderboard

    That worked a treat thanks, it has given me my best result yet. The last issue that I am having is because of where I am getting the data from if you check the 'Leaderboards page I have the 10 names populated from previous sheets but I then have to paste the 5 intermediate names, these paste in a slightly different format and dont include the riders team tag. The issue with this is when i do conditional formatting to show duplicates in the 'Results and Overall it wont pick these up as duplicates.

    The other issue is the Riders name can change from race to race due to them using different team tags, their names stay the same but the tag on the end changes which also doesnt flag up as a duplicate in the 'Results and Overall.

    So I have to manually go through and check for similar names and copy and paste the values into the intermediate column so it flags up as a duplicate.

    Is there a way to do conditional formatting to show similar names for example these are the same person but with different team tag, I need to be able to see that they are the same in the 'Results and Overall sheet so I can manually delete them which i am fine doing but id like a clear visual, e.g. it highlighted red if they are similar


    *Richard Shoebridge (EVO EXPRESSO)EVOLUTION
    Richard Shoebridge (EVO EXPRESSO)

    Many thanks

    I have attached 2 files, the copy is before I manually sort it

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Help with a Racing Leaderboard

    Perhaps the following will help in that regard:
    Insert a new column (A) on the Results and Overall sheet and populate using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The following conditional formatting formula identifies duplicates: =AND(C2<>"",COUNTIFS(A:A,A2)>1)
    I also used the following in column AI to produce a list of distinct names:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

+ 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. Leaderboard
    By kirbster123 in forum Excel General
    Replies: 2
    Last Post: 12-12-2018, 02:37 AM
  2. [SOLVED] Horse racing?
    By Yancx in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-01-2017, 12:51 AM
  3. Replies: 4
    Last Post: 07-07-2013, 02:50 PM
  4. Making a leaderboard in excel
    By mmaher5 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-15-2012, 03:17 PM
  5. Daily Racing Form: Horse racing
    By mehtalmann in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-11-2006, 04:40 PM
  6. Racing League needs help
    By Locomotion in forum Excel General
    Replies: 2
    Last Post: 03-15-2006, 11:46 PM
  7. [SOLVED] racing data:/help please
    By Mike in forum Excel General
    Replies: 2
    Last Post: 03-15-2006, 01:25 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