PGA Tour Excel Spreadsheet with money list and FedEx Cup

[GoBraves21]

Hi all,

I've been doing some research as to summing values from multiple sheets, but I haven't found an effective way to do what's in the title. Basically I plan to import player leaderboards from each tournament into separate sheets, including money winnings and FedEx cup points. I play the Links 2003 game, so I want to play along with the pros for this season. So, lets say for each tournament sheet I have set it up for the players' names to be in column B and their money winnings for the tournament in column F and FedEx points in G. How could I set up a sheet for it to recognize each player's total money and points separately and put them in separate sheets in order of who is leading each of those categories, or would I need to do this manually for each player? I'll plan on having a first sheet called first and a last one called last just to make things simpler.

If I can't get them to automate and have to figure each player manually, what formula could I use? Let's say I want to sum Dustin Johnson's money in one sheet and points in another sheet, and he's won $1,000,000 in one tournament, and $500,000 in another, and 500 points in one tournament and 250 in another.

This seems like a great community by the way and thanks in advance!

 

[MrTeeny]

As long as your player names are always in the same format and the data is in a regular format it should be easy enough to code some VBA to loop thru each sheet and sum the totals as it goes thru them, then dump the data back to the main sheet with totals and names and sort the data as you wish.

All the code has to do is read the names in say column A in sheet1 then loop thru the remaining worksheets and check for matches, once it finds a match it sums the totals on the corresponding row and once all sheets are finished dumps the data back into the main sheet1 and move to the next name in say A2 etc etc once all names have been read it can simply reorder by highest amounts.

 

[GoBraves21]

I'm totally new to advanced Excel things like VBA. Is there a good forum post to check out or a good YouTube video to recommend, or could anyone give a short tutorial of how to start this project? Thanks.

 

[MrTeeny]

https://www.guru99.com/vba-tutorial.html is probably good as a starter but I'd guess there are lots of sites on the web. Plus searching this forum for things you need is as good as anywhere to start

Here's some code to get you started showing how you can gather all the names off each each worksheet in the workbook and stick the unique values in column A on the first sheet. From there you could just read thru the list and add up any values from the other sheets for each name. Obviously with excel there are many ways to achieve the same aim but the better ones, like sticking everything into arrays and collections, may get a bit complicated for anyone starting out so it's usually best to work your way up from simple loops and move on when you understand each step

Sub InsertSummaryNames()
Dim WSheet As Worksheet
Dim LastRow As Long
Dim LastRowData As Long
Dim i As Long, j As Long: j = 1
<code class="lang-vb hljs vbnet expanded">Application.ScreenUpdating = False </code>
        
    For Each WSheet In Worksheets
    If WSheet.Index > 1 Then ' ignore the summary sheet
       
    With WSheet  'looping thru each remaining sheet
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row ' find our last cell of names on the sheet so we can loop thru them
    LastRowData = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row ' find our last cell of names on the summary  sheet so we can see if it's already doen
   
   
    For i = 1 To LastRow
    If .Range("A" & i).Value <> "" And IsError(Application.Match(.Range("A" & i).Value, Sheets(1).Range("A1:A" & LastRowData + 1).Value, 0)) Then ' no match so let's add it
    Sheets(1).Range("A" & j).Value = .Range("A" & i).Value
    j = j + 1
    End If
                    
    Next i
    End With

    End If
    Next WSheet
<code class="lang-vb hljs vbnet expanded">  Application.ScreenUpdating = True
</code>    End Sub

 

[GoBraves21]

I really appreciate the code as I ran it and got the list of players who have played in events so far this season. I only had to change column A to B. I still don't understand just how to sum up those values across multiple sheets. Is there a way to upload my spreadsheet as I think this would be the best way to show just what I have? Thank you.