VBA or Excel Formula to search football data to create list

[mattadams84]

Hi,

I am looking for some help. Each day i receive a CSV file with lots of data in for football matches. I am looking to create either a VBA Macro or perhaps just plain excel formulae to create a list from this data.

Basically each row contains a football fixture, and the columns (of which there are 294) contains data relating to that specific fixture.

I want to be able to search through each fixture and create a list from the whole of the data when certain criteria are met.

So for example, I want to be able to say that :



IF for the fixture (in the row) that the value of column 75 is greater than 30

AND that the value of column 100 is greater than 20

AND that the value of Column 120 is less than the value of column 121

AND that the value of Column 221 + the Value of column 220 is less than 20

Then add it to my list....



The list would just display the fixture on a different sheet.

I have a pretty decent understanding of VBA and excel formulae so can decipher code, but i am not too proficient at writing it. If anyone can help then id be v grateful. I have linked to a CSV file

http://www.dropbox.com/s/zo6yhba0xw98a6o/excel_tsd_2018-06-23.csv?dl=0

Thx

 

[mumps]

Try:

Sub FHTrades()
    Application.ScreenUpdating = False
    Dim LastRow As Long, fs As Worksheet, ds As Worksheet, x As Long
    Set fs = Sheets("Filters")
    Set ds = Sheets("Data")
    LastRow = ds.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 3 To LastRow
        If ds.Cells(x, 40) >= fs.Range("C2") And ds.Cells(x, 41) >= fs.Range("C2") And ds.Cells(x, 42) >= fs.Range("C5") And ds.Cells(x, 43) >= fs.Range("C6") And ds.Cells(x, 44) >= fs.Range("C7") Then
            ds.Cells(x, 4).Resize(, 2).Copy Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "B").End(xlUp).Offset(1, 0)
            ds.Cells(x, 42).Resize(, 3).Copy Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "D").End(xlUp).Offset(1, 0)
            ds.Cells(x, 81).Copy Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "I").End(xlUp).Offset(1, 0)
            ds.Cells(x, 91).Copy Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "J").End(xlUp).Offset(1, 0)
            Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "G").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 144) & "/" & ds.Cells(x, 40)
            Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "H").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Sum(ds.Cells(x, 53).Value + ds.Cells(x, 67).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[mattadams84]

Brilliant. Thank you very much. I have now got the sheet doing pretty much exactly what i want it to. I have run in to a bug though. Here is the new code:

Sub FHTrades()    Application.ScreenUpdating = False
    Dim LastRow As Long, fs As Worksheet, ds As Worksheet, x As Long
    Set fs = Sheets("Filters")
    Set ds = Sheets("Data")
    LastRow = ds.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ClearSelections
    For x = 3 To LastRow
        If ds.Cells(x, 40) >= fs.Range("C2") And ds.Cells(x, 41) >= fs.Range("C2") And ds.Cells(x, 42) >= fs.Range("C5") And ds.Cells(x, 43) >= fs.Range("C6") And ds.Cells(x, 44) >= fs.Range("C7") And WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 23).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) <= fs.Range("C8") Then
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "B").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 1)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "C").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 4)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "D").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 5)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "E").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 42).Value & "% (" & WorksheetFunction.Round(ds.Cells(x, 40).Value / 100 * ds.Cells(x, 42).Value, 0) & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "F").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 43).Value & "% (" & WorksheetFunction.Round(ds.Cells(x, 41).Value / 100 * ds.Cells(x, 43).Value, 0) & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "G").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 44).Value & "%"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "H").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "I").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 144).Value + ds.Cells(x, 159).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 144).Value + ds.Cells(x, 159).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "J").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 147).Value + ds.Cells(x, 162).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 147).Value + ds.Cells(x, 162).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "K").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 60).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 60).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "L").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 74).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 74).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "M").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 101).Value * (ds.Cells(x, 115).Value / 100) + ds.Cells(x, 102).Value * (ds.Cells(x, 117).Value / 100) + ds.Cells(x, 121).Value * (ds.Cells(x, 135).Value / 100) + ds.Cells(x, 122).Value * (ds.Cells(x, 137).Value / 100)) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value), 2)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "N").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 81)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "O").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 91)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "P").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 82)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 92)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "R").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 83)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "S").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 93)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "T").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 88)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "U").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 98)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub


Sub SHTrades()
    Application.ScreenUpdating = False
    Dim LastRow As Long, fs As Worksheet, ds As Worksheet, x As Long
    Set fs = Sheets("Filters")
    Set ds = Sheets("Data")
    LastRow = ds.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ClearSelections
    For x = 3 To LastRow
        If ds.Cells(x, 40) >= fs.Range("C2") And ds.Cells(x, 41) >= fs.Range("C2") And ds.Cells(x, 45) >= fs.Range("F5") And ds.Cells(x, 46) >= fs.Range("F6") And ds.Cells(x, 47) >= fs.Range("F7") And WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) <= fs.Range("F8") Then
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "B").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 1)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "C").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 4)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "D").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 5)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "E").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 57).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 57).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "F").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 71).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 71).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "G").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 58).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 58).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "H").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 72).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 72).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "I").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 47).Value & "%"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "J").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "K").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 54).Value + ds.Cells(x, 68).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 54).Value + ds.Cells(x, 68).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "L").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 55).Value + ds.Cells(x, 69).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 55).Value + ds.Cells(x, 69).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "M").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 59).Value + ds.Cells(x, 73).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 59).Value + ds.Cells(x, 73).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "N").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 62).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 62).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "O").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 76).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 76).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "P").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 179).Value + ds.Cells(x, 193).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 179).Value + ds.Cells(x, 193).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) / WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "R").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 101).Value + ds.Cells(x, 102).Value + ds.Cells(x, 121).Value + ds.Cells(x, 122).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value), 2)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "S").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 81)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "T").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 91)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "U").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 82)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "V").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 92)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "W").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 83)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "X").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 93)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Y").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 88)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Z").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 98)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

This works well for the most part but i am getting an Overflow Error 6 error on this line :

Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) / WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) & ")"

Now, i know why i am getting this error because for one of the matches, the data in the columns that it is trying to calculate is 0. Basically the VBA is trying to do (0+0)/(0+0). Obviously this is going to throw up an error. Is there anyway to get around it this in VBA. So basically if, for one of the matches, where the numbers in those columns are 0 and therefore it tries to calculate with just 0's then instead of doing the calculation it will insert the Value "N/A"...

I have updated the spreadsheet in Dropbox. You will see that this error is being caused by the match Heerenveen vs Fortuna Sittard.

https://www.dropbox.com/s/mlwss7s3ueqx6o7/Filters.xlsm?dl=0