Thread: Last six league goals calculation

Welcome to the forum, embwabili.

Both this and your other post look a lot like homework problems. We don't do those, but will help if you have a specific question other than, "How do I do this?"

Hello embwabili,

Welcome to the Forum!

The attached workbook has a button to run the macro. When the macro runs it will fill in the worksheet columns "G:J" with the scores of the 6 most recent games. All the data is first sorted by date in descending order and then alphabetically by team. Here is the macro that has been added...

'Written: March 15, 2010
'Author:  Leith Ross

Sub LoadTeamScores()

  Dim Cell As Range
  Dim Cnt As Integer
  Dim DataRng As Range
  Dim DataRow1 As String
  Dim Goals As Variant
  Dim Item As Variant
  Dim Key As String
  Dim RngEnd As Range
  Dim Teams As Object
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Sheet1")
    DataRow1 = "A2:F2"
    
    Set DataRng = Wks.Range(DataRow1)
    Set RngEnd = Wks.Cells(Rows.Count, DataRng.Column).End(xlUp)
    If RngEnd.Row < DataRng.Row Then Exit Sub
    
    Set DataRng = Wks.Range(DataRng, RngEnd)
      
      Set Teams = CreateObject("Scripting.Dictionary")
      Teams.CompareMode = vbTextCompare
         
      DataRng.Offset(-1, 0).Sort _
              Key1:=DataRng.Cells(1, 1), Order1:=xlDescending, _
              Key2:=DataRng.Cells(1, 2), Order2:=xlAscending, Header:=xlYes, _
              MatchCase:=False, Orientation:=xlTopToBottom, _
              DataOption1:=xlSortNormal
       
        For Each Cell In DataRng.Columns(2).Cells
          Key = Trim(Cell)
          Set Item = Cell.Offset(0, 2).Resize(1, 2)
          If Key <> "" Then
            If Not Teams.Exists(Key) Then
               Teams.Add Key, "1," & Item(1).Text & "," & Item(2).Text
            Else
               X = Cell.Address
               Goals = Split(Teams(Key), ",")
               Cnt = Goals(0)
                 If Cnt < 6 Then
                    Goals(0) = Cnt + 1
                    Goals(1) = Goals(1) + Item(1)
                    Goals(2) = Goals(2) + Item(2)
                    Teams(Key) = Join(Goals, ",")
                 End If
            End If
          End If
        Next Cell
         
        For R = 1 To DataRng.Rows.Count
          With DataRng
            Goals = Split(Teams(.Item(R, 2).Text), ",")
              .Item(R, 7).Value = Goals(1)
              .Item(R, 8).Value = Goals(2)
            Goals = Split(Teams(.Item(R, 3).Text), ",")
              .Item(R, 9).Value = Goals(1)
              .Item(R, 10).Value = Goals(2)
          End With
        Next R
    
End Sub

Needless to say it generally helps if you provide expected results - this way everyone can validate their logic (eg "current" game included in the last n results ?)

I appreciate you have (seemingly) requested code based solution given Forum in which this is located but just to illustrate that you can (if so desired) resolve with formulae see attached.

On very large data sets it would make sense to use more helpers to negate need for Arrays / SUMPRODUCT etc... a UDF / Sub Routine is not a bad idea

To reiterate - I'm not advocating this above and beyond a VBA based solution - merely an alternative.

EDIT: upload removed and replaced by version in latter post (#10)

embwabili, I think you misunderstood my point... you should provide expected results in your sample file at least for a few cells.
Not doing so means people are guessing based on interpretation - they having nothing empirical against which to measure their own results.

Originally Posted by embwabili

DonkeyOte, are the formulae you put in the sheet examples or they are the ones I should use in verification?

The prior attachment was pretty self explanatory I think, however, given the below:

Originally Posted by embwabili

summing all the goals scored by a home team(col b) in last six previous appearances before the current fixture

they will need to be "tweaked" given I had incorrectly presumed "current" result to be inclusive as opposed to exclusive (hence earlier point regards providing expected results).

To tweak you must change the 5 to a 6 in the SMALL arrays in L2 & M2 - remembering to reset the Array (CTRL + SHIFT + ENTER) - then copy down over remaining rows.
Adjust G:J by removing reference to current score if not to be included (ie remove $D2+ etc...)

see revision
(earlier version will be removed for space)

For all purposes and intents, this thread canbe considered solved and closed. Thanks so much DonkeyOte