Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Combined ranking from multiple workbooks
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    I'm at a bit of a loss, why do you need a formula in column H, when you can have the macro calculate whatever you want there? The first line starting with Licenses(r, 6) puts in what you want if there's no change, the next line starting with Licenses(r, 6) calculates the difference (if any). We could also add a bit of code to put something different there if it's a newly added player.

    If you still want a formula, you can enter it like this:

    Code:
    Licenses(r, 6) = "=IF(RC[-1]<50,"""",""-"")"
    I'd recommend the R1C1 style of cell reference to avoid having to calculate rows and columns.
    Last edited by Eric W; Nov 6th, 2018 at 11:37 AM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  2. #12
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Hi Eric,

    My apologies - I'm not great with Excel, so I must have misunderstood you. If the macro can calculate what I want there, then of course there's no problem at all

    The column is used to show the difference in the players's score after each update, so here's the formula that's in there now:

    Code:
    =IFERROR((ROUND(G4;2)-(ROUND(J4;2)));"NEW")
    If the macro can just do the same, that would be great

  3. #13
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Yes, the macro can do that:

    Code:
    Public Sub RankScores()
    Dim MyPath As String, MyName As String, Status As String
    Dim MyRow As Long, r As Long, i As Long, lnum As Long, Dict As Object
    Dim Licenses(1 To 20000, 1 To 9), wktab As Variant, avg As Double, ix As Long, lr As Long
    Dim L2() As Variant
    
    ' Initialization
        MyPath = "C:\Users\eweeks\Documents\Excel\Students\"
        
        Set Dict = CreateObject("Scripting.Dictionary")
        
        MyName = Dir(MyPath & "*.xl*")
        MyRow = 0
        
        Application.ScreenUpdating = False
        
    ' Find all the matching files in this directory.  get the scores
        On Error GoTo CloseIt:
        Do While MyName <> ""
            Workbooks.Open Filename:=MyPath & MyName
            Sheets("Samlet rangliste").Select
            avg = Range("Y7").Value
            wktab = Range("A1").Resize(Cells(Rows.Count, "D").End(xlUp).Row, 15).Value
            savmr = MyRow
            For r = 4 To UBound(wktab)
                lnum = wktab(r, 4)
                If Not Dict.exists(lnum) Then
                    MyRow = MyRow + 1
                    Dict.Add lnum, MyRow
                    ix = MyRow
                    Licenses(ix, 1) = ix
                    Licenses(ix, 2) = lnum
                    Licenses(ix, 3) = wktab(r, 5)
                    Licenses(ix, 4) = wktab(r, 6)
                Else
                    ix = Dict(lnum)
                End If
                Licenses(ix, 6) = Licenses(ix, 6) + wktab(r, 11) + wktab(r, 12)
                Licenses(ix, 5) = Licenses(ix, 5) + avg * (wktab(r, 11) + wktab(r, 12)) * wktab(r, 15)
            Next r
    NextFile:
            ActiveWorkbook.Close savechanges:=False
            MyName = Dir()
        Loop
    
    
    ' Finalize scores, get previous scores
    
    
        On Error Resume Next
        lr = Cells(Rows.Count, "D").End(xlUp).Row
        wktab = Range("D4:G" & lr).Value
        ReDim L2(1 To MyRow, 1 To 9)
        For r = 1 To MyRow
            Licenses(r, 5) = Licenses(r, 5) / Licenses(r, 6)
            Licenses(r, 6) = "New"
            Licenses(r, 8) = "-"
            Licenses(r, 9) = "-"
            For i = 1 To UBound(wktab)
                If wktab(i, 1) = Licenses(r, 2) Then
                    Licenses(r, 6) = "No change"
                    If Licenses(r, 5) <> wktab(i, 4) Then
                        Licenses(r, 6) = WorksheetFunction.Round(Licenses(r, 5) - wktab(i, 4), 2)
                    End If
                    Licenses(r, 8) = wktab(i, 4)
                    Licenses(r, 9) = i
                    Exit For
                End If
            Next i
            For i = 1 To 9
                L2(r, i) = Licenses(r, i)
            Next i
        Next r
        
        Range("C4").Resize(UBound(L2), UBound(L2, 2)) = L2
        
    ' Sort the scores
        With ActiveSheet.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("G4"), Order:=xlDescending
            .SetRange Range("D4:K" & UBound(L2) + 3)    ' The 3 means 3 rows before the first data row
            .Apply
        End With
            
    ' finalization
        Application.ScreenUpdating = True
        Exit Sub
        
    CloseIt:
        Resume NextFile:
    
    
    End Sub
    The first red line will be what shows up in that column for a new player. The next red line is for an existing player with no score change, and the last red line calculates the change, if any.

    I also found one minor glitch with the sort that I fixed.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #14
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Hi Eric,

    Excellent stuff, this is getting very close to being what I hoped for!

    One question - some players change clubs mid-season, so may be listed at one club in one division and at another club in another division (but of course with the same unique license number). How will that affect the player listing here? Will it just list the player with the first club name found when scanning the target worksheets?

  5. #15
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Exactly. The first time the macro encounters an ID number, it saves the club name. When it encounters the ID in another workbook, it just updates the numbers needed to calculate the rating. When it gets to the point of updating the summary workbook, again it just searches for the ID to find the previous rating, so the club name will be what it found from the first workbook. So to be sure to get the current club name, you'll need to change it in all of the source workbooks.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #16
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Great, thank you. I think this should be ready for putting out there!

    Once I get this going, there's bound to be people who want something changed or added, so if they make any requests I deem sensible I might pop in here and bug you again at some point

    Thank you once again for your work!

  7. #17
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Hi @Eric W,

    First of all, I'd like to say thank you once again for the work you did for me last year - it worked like a charm throughout the season!

    Now, during the last year I have updated the layout of my workbooks to make it look a bit better, and this included formatting the tables as actual tables in the workbooks. This has naturally caused both the macro you made for me and the one @B___P made for me in this thread to not work as intended anymore.

    Would it by any chance be possible to adjust the macros so they work with tables? Here are a few screenshots of what the workbooks look like now:



    This is the target sheet in the workbook from my first thread. Column B is not a table, Column C is a table by itself so the rank doesn't switch around when sorting, and Column D to V is the main table. So when the results are added from the other sheet (see screenshot below), the data should be updated in the same way as before for already existing players, and for new players it should add a row in Column B, add a table row in Column C and add table rows for the main table and insert the data in the new row.



    This is the sheet where the data from each round is entered (I make a new sheet for each date). The data from this should be collated into the aforementioned sheet. As before, Column B is a table by itself and Column C to P is the main table with the data.

    And finally, below is a screenshot of what the sheet from this thread looks like now:



    Column C is once again a table by itself, while Column D to L is the table with the data. As you can see, I have added a 'Darts per leg won' column (Column G), as this has been requested by many of the players following this ranking. If possible, it would be great if the macro could gather the data for this column by going through the target workbooks like before, adding all numbers in the column 'Darts' (Column R in the sheet Samlet rangliste) for a specific license number, and dividing it by the summed number of the column 'Legs won' (Column K in the sheet Samlet rangliste), so that way we get the average number of darts used per leg won across all the leagues the specific player has played in.

    If any of the posters that have helped me before are still active, any help would be much appreciated!

  8. #18
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Hi, I am still active. The problem is that this appears to require a fair bit of time to work on. Compounding the problem is the fact that my PC crashed last year, and I lost the test files I had. Recreating those would be time-consuming too. In any event, I'd probably need to see copies of your new workbooks anyway to see the table names and other changes. I'm pretty busy right now, so I may not be able to help this time around. You might want to open a new thread so that others can see it and maybe offer to help. By all means post the existing code so they can just modify it.

    Good luck!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #19
    New Member
    Join Date
    May 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combined ranking from multiple workbooks

    Hi Eric,

    Thanks for your reply - and sorry to hear about your PC crash and subsequent data loss! If at any point you do get some more time on your hands, I'd be happy to send you the workbooks needed.

    I'll try to start a new thread in the coming days and see if anyone else is able to help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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