Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Tidying up worksheet to get rid of "junk" information

Hi Guys,

Attched is a work book.

Sheet1 shows a list of horse races with stats that i pull from the internet - at the moment i have to go through manualy to make my selctions but i would like to have this process automatic.

I would like it so when i copy and paste the data into sheet1 sheet to can read the data from sheet one and make it neat!!!

Basicly i need to strip out the 'junk' and put it together shown in tab2. I have also added an extra colum 'Race Num' this will need to have the race number in so race 1,2,3,4,5 ect.

The issue is each selection will be different depending on the number of horses in the race BUT it will always begin with the header titles for each race "No.      Form      Days      Horse      Age      Weight      Headgear      Jockey      Trainer      OR      FC Odds      HRB Total      CD Form
" and will and with a blank column then "Last Years Winner"      .

Hopefuly i have given enough information to hopefully get an answer to how this can be done.
J.P.B.S.xlsx
0
runnerjp2005
Asked:
runnerjp2005
  • 6
  • 6
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

Sub macro1()

Application.ScreenUpdating = False
    ActiveWorkbook.Worksheets("Info").Copy After:=Sheets(Sheets.Count)
    Set ResultSht = Sheets(Sheets.Count)
    ResultSht.Name = "Result"
    Range("1:4").EntireRow.Delete
    Range("N1") = "Number"
    Idx = 1
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    rwIdx = 2
    AreaIsOn = True
    Do While rwIdx <= lastRow
    If Cells(rwIdx, 1) = "No." Then
        AreaIsOn = True
        Cells(rwIdx, 1).EntireRow.Delete
        Idx = Idx + 1
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1

    ElseIf AreaIsOn = True And Cells(rwIdx, 1) = "" Then
        AreaIsOn = False
        Cells(rwIdx, 1).EntireRow.Delete
        Debug.Print Cells(rwIdx, 1).End(xlDown).Row
    ElseIf AreaIsOn = False And (Cells(rwIdx + 1, 1) <> "" Or Cells(rwIdx + 2, 1) <> "" Or Cells(rwIdx + 3, 1) <> "") Then
        Cells(rwIdx, 1).EntireRow.Delete
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Else
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1
    End If
    Loop
ResultSht.Range("A1").Activate
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
Copy-of-J.P.B.xlsm
0
 
runnerjp2005Author Commented:
Thats amazing! this is exactly what i wanted thanks!

Just one thing.... instead of creating a sheet named reults each time (ResultSht.Name = "Result"
) could it just replace the data in results with the new data???
0
 
Rgonzo1971Commented:
Hi

If you prefer I do not recommend it because if the macro as a problem you won't recover your data with undo

Sub macro1()

Application.ScreenUpdating = False
    Sheets("Info").Activate
    Range("1:4").EntireRow.Delete
    Range("N1") = "Number"
    Idx = 1
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    rwIdx = 2
    AreaIsOn = True
    Do While rwIdx <= lastRow
    If Cells(rwIdx, 1) = "No." Then
        AreaIsOn = True
        Cells(rwIdx, 1).EntireRow.Delete
        Idx = Idx + 1
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1

    ElseIf AreaIsOn = True And Cells(rwIdx, 1) = "" Then
        AreaIsOn = False
        Cells(rwIdx, 1).EntireRow.Delete
        Debug.Print Cells(rwIdx, 1).End(xlDown).Row
    ElseIf AreaIsOn = False And (Cells(rwIdx + 1, 1) <> "" Or Cells(rwIdx + 2, 1) <> "" Or Cells(rwIdx + 3, 1) <> "") Then
        Cells(rwIdx, 1).EntireRow.Delete
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Else
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1
    End If
    Loop
    Range("N1").End(xlDown).ClearContents
    Range("A1").Activate
Application.ScreenUpdating = True
End Sub

Open in new window


Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
runnerjp2005Author Commented:
Sorry i ment it on a sep tab...so keep the results tab but you would need to delete it each time i ran the original code.
0
 
Rgonzo1971Commented:
Hi

Here we are

Sub macro1()

Application.ScreenUpdating = False
    On Error Resume Next
    Set ResultSht = Sheets("Result")
    On Error GoTo 0
    If Not (IsEmpty(ResultSht)) Then
        Application.DisplayAlerts = False
        Sheets("Result").Delete
        Application.DisplayAlerts = True
    End If
    ActiveWorkbook.Worksheets("Info").Copy After:=Sheets(Sheets.Count)
    Set ResultSht = Sheets(Sheets.Count)
    ResultSht.Name = "Result"
    Range("1:4").EntireRow.Delete
    Range("N1") = "Number"
    Idx = 1
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    rwIdx = 2
    AreaIsOn = True
    Do While rwIdx <= lastRow
    If Cells(rwIdx, 1) = "No." Then
        AreaIsOn = True
        Cells(rwIdx, 1).EntireRow.Delete
        Idx = Idx + 1
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1

    ElseIf AreaIsOn = True And Cells(rwIdx, 1) = "" Then
        AreaIsOn = False
        Cells(rwIdx, 1).EntireRow.Delete
    ElseIf AreaIsOn = False And (Cells(rwIdx + 1, 1) <> "" Or Cells(rwIdx + 2, 1) <> "" Or Cells(rwIdx + 3, 1) <> "") Then
        Cells(rwIdx, 1).EntireRow.Delete
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Else
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1
    End If
    Loop
    Range("N1").End(xlDown).ClearContents
    Range("A1").Activate
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 
runnerjp2005Author Commented:
Thats brilliant thanks!!!

Could I be cheeky and ask one more thing ... can i have anouther row added where it add's a + j / 2 (a2+j2/2)

So after number it will have total and it will contain a + j / 2
0
 
Rgonzo1971Commented:
Hi
Could you be more precise with example

Regards
0
 
runnerjp2005Author Commented:
An example is attached....

I have also noticed if you look at line 619 in info it adds an extra colum "stall"
This messes up the result seaction (line 296) is there away that this can be removed when copying over to  results???
J.P.B.S.xlsx
0
 
Rgonzo1971Commented:
Let's see

Sub macro1()

Application.ScreenUpdating = False
    On Error Resume Next
    Set ResultSht = Sheets("Result")
    On Error GoTo 0
    If Not (IsEmpty(ResultSht)) Then
        Application.DisplayAlerts = False
        Sheets("Result").Delete
        Application.DisplayAlerts = True
    End If
    ActiveWorkbook.Worksheets("Info").Copy After:=Sheets(Sheets.Count)
    Set ResultSht = Sheets(Sheets.Count)
    ResultSht.Name = "Result"
    Cells.NumberFormat = "General"
    Range("1:4").EntireRow.Delete
    Range("N1") = "Number"
    Range("O1") = "Total"
    Idx = 1
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    rwIdx = 2
    AreaIsOn = True
    Do While rwIdx <= lastRow
    If Cells(rwIdx, 1) = "No." Then
        AreaIsOn = True
        Cells(rwIdx, 1).EntireRow.Delete
        Idx = Idx + 1
        Range("N" & rwIdx).Value = Idx
        Range("O" & rwIdx).Formula = "=(J" & rwIdx & "+L" & rwIdx & ")/2"
        rwIdx = rwIdx + 1

    ElseIf AreaIsOn = True And Cells(rwIdx, 1) = "" Then
        AreaIsOn = False
        Cells(rwIdx, 1).EntireRow.Delete
    ElseIf AreaIsOn = False And (Cells(rwIdx + 1, 1) <> "" Or Cells(rwIdx + 2, 1) <> "" Or Cells(rwIdx + 3, 1) <> "") Then
        Cells(rwIdx, 1).EntireRow.Delete
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Else
        Range("N" & rwIdx).Value = Idx
        Range("O" & rwIdx).Formula = "=(J" & rwIdx & "+L" & rwIdx & ")/2"
        rwIdx = rwIdx + 1
    End If
    Loop
    Range("N1").End(xlDown).ClearContents
    Range("A1").Activate
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 
runnerjp2005Author Commented:
Spot on but it still has an issue:

No.      Form      Days      Horse      Age      Weight      Headgear      Jockey      Trainer      Stall      OR      FC Odds      HRB Total      CD Form

Some of the stats have a column with stall in it... i need it to ingnor that when it copies over to the results page (if you look at row 619 you will see what i mean)
0
 
Rgonzo1971Commented:
Here Stall Deleted

Sub macro1()

Application.ScreenUpdating = False
    On Error Resume Next
    Set ResultSht = Sheets("Result")
    On Error GoTo 0
    If Not (IsEmpty(ResultSht)) Then
        Application.DisplayAlerts = False
        Sheets("Result").Delete
        Application.DisplayAlerts = True
    End If
    ActiveWorkbook.Worksheets("Info").Copy After:=Sheets(Sheets.Count)
    Set ResultSht = Sheets(Sheets.Count)
    ResultSht.Name = "Result"
    Cells.NumberFormat = "General"
    Range("1:4").EntireRow.Delete
    Range("N1") = "Number"
    Range("O1") = "Total"
    Idx = 1
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    rwIdx = 2
    AreaIsOn = True
    Do While rwIdx <= lastRow
    If Cells(rwIdx, 1) = "No." Then
        AreaIsOn = True
        If Range("J" & rwIdx) = "Stall" Then
            Range(Range("J" & rwIdx), Range("J" & rwIdx).End(xlDown)).Delete Shift:=xlToLeft
        End If
        Cells(rwIdx, 1).EntireRow.Delete
        Idx = Idx + 1
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1

    ElseIf AreaIsOn = True And Cells(rwIdx, 1) = "" Then
        AreaIsOn = False
        Cells(rwIdx, 1).EntireRow.Delete
    ElseIf AreaIsOn = False And (Cells(rwIdx + 1, 1) <> "" Or Cells(rwIdx + 2, 1) <> "" Or Cells(rwIdx + 3, 1) <> "") Then
        Cells(rwIdx, 1).EntireRow.Delete
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Else
        Range("N" & rwIdx).Value = Idx
        rwIdx = rwIdx + 1
    End If
    Loop
    Range(Range("O2"), Range("O" & Range("A1").End(xlDown).Row)).FormulaR1C1 = "=(RC[-5]+RC[-3])/2"
    Range("N1").End(xlDown).ClearContents
    Range("A1").Activate
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 
runnerjp2005Author Commented:
Amazing!!!

If i could give you more i would - just as i imagined ... i can easy work with the tidied up sheet!!!

Thanks so much
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now