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]

What do you mean by "fixture"? Please give us a few examples from your data.

 

[mattadams84]

By fixture i mean a match for example England vs Spain. An example of the data can be found in the CSV file attached to the dropbox. I dont know how i can attach it here...

 

[mumps]

Insert a blank sheet and name it "List". Try this macro:

Sub CreateList()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    Sheets("List").Range("A1:B1") = Array("Home", "Away")
    For x = 2 To LastRow
        If Cells(x, 75) > Cells(x, 30) And Cells(x, 100) > Cells(x, 20) And Cells(x, 120) < Cells(x, 121) And WorksheetFunction.Sum(Cells(x, 221) + Cells(x, 220) < Cells(x, 20)) Then
            Cells(x, 4).Resize(, 2).Copy Sheets("List").Cells(Sheets("List").Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

Save the workbook as a macro-enabled file.

 

[mattadams84]

Brilliant, Thanks v much. I have altered the code slightly as to make it work with my sheet. I am struggling with a couple of things. I would like to copy the data to a different place on the sheet. I would like the list to start in B3 for the home team, and C3 for the away team. I tried changing the offset but to no avail.

I would also like to copy over other data if a match passes the criteria. For example if a match is copied to the list i would as well as the team names, be able to copy the data in columns 43,44,45 to the list (to D3,E3,F3)...

Thanks a lot for your help in any case !

Sub FHTrades()    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    For x = 3 To LastRow
        If Cells(x, 40) >= Worksheets("Filters").Range("C2") And Cells(x, 41) >= Worksheets("Filters").Range("C2") And Cells(x, 42) >= Worksheets("Filters").Range("C5") And Cells(x, 43) >= Worksheets("Filters").Range("C6") And Cells(x, 44) >= Worksheets("Filters").Range("C7") Then
            Cells(x, 4).Resize(, 2).Copy Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Try:

Sub FHTrades()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet, x As Long
    Set ws = Sheets("Filters")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Selections").Range("B2:C2") = Array("Home", "Away")
    Sheets("Selections").Range("D2:F2") = Array("MTS A1 %", "MTS Pr1 %", "MTS H2 %")
    For x = 3 To LastRow
        If Cells(x, 40) >= ws.Range("C2") And Cells(x, 41) >= ws.Range("C2") And Cells(x, 42) >= ws.Range("C5") And Cells(x, 43) >= ws.Range("C6") And Cells(x, 44) >= ws.Range("C7") Then
            Cells(x, 4).Resize(, 2).Copy Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "B").End(xlUp).Offset(1, 0)
            Cells(x, 43).Resize(, 3).Copy Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "D").End(xlUp).Offset(1, 0)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[mattadams84]

Thanks very much, I am pretty sure i have deciphered how your code works. I was confused about the resize and what it does, but i got it now! I have one very last thing to ask... I have updated the code to copy other data over to the cells. However is it possible to perform calculations. For example in the selections sheet for each match in Column H i would like it to show the following data:

((Column 53 + Column 67) / (Column 40 + Column 41)) * 100

I dont want to put this as a formula in the cell, id prefer the calculation to be done in VBA if possible.

My aim is that afterwards in a cell i will be able to do this:

Sheets("Selections").Cells(Sheets("Selections").Rows.Count, "G").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 144) & "/" & ds.Cells(x, 40) &"/" & THE CALCULATION

Here is the whole code updated:

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)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

Hope this makes sense. I am very grateful for all of your help. It is much appreciated!

 

[mattadams84]

I guess i should also add that i will likely want to do multiple calculations concerning different cells too, if that makes any difference.

So as well as:
((Column 53 + Column 67) / (Column 40 + Column 41)) * 100

i will probably want to do (for example)

(Column 73 / 41) * 100

I dont want to limit myself to just one calculation...

The reason for doing this is because there is so much data, i need to synthesise some and want to display it in just one cell afterwards...

 

[mumps]

What do you mean by "each match in column H"? Can you give a few examples from your data? Column H in the "excel_tsd_2018-06-23 (3) sheet" is currently blank. Also there is no "Filters" sheet. Maybe you could upload a current version of the file that contains more data and the missing sheet.

 

[mattadams84]

Sure, sorry, it would be much easier if you saw my current file

Here is the link to a dropbox.

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

As you can see i have separated the data, the selections and filter sheets.

When i talk about column H i mean on the selections page.