Help with Macro to sort BBC football fixtures

[tommyleinen]

Hi all,

The BBC updated their sports pages overnight to a new format which puts my football predictor leagues in a bit of a pickle due to the way the scores etc all run on formulas driven by the BBC fixture format. I think you might call that an achilles heal - or just plain stupidity!

What I need is a macro to sort the new style into the old style, so for example if I paste values of the current format down column A (due to their format this would take up columns A-C). I then need something that can sort them into one column, be it D, or insert a new column A for tidiness' sake. You can find the fixture list here: http://www.bbc.co.uk/sport/football/league-one/fixtures

If you highlight, copy, andtry pasting the values into excel you will see what I mean, the old format is a single cell: Walsall v Notts County, 19:45

Also, at the top of each day's fixtures was the day in the format below:
Tuesday, 31 January 2012
Walsall v Notts County, 19:45

At the end of each day, it needs to skip 2 cells and start the next day's fixtures.

I know a little vba, but not really enough for this to take less than about 10hrs!

If any talented soul could please take a look at this I would be very grateful!

 

[bertie]

I am not quite sure what you are trying to do, but have you tried using a different browser?

Using Google Chrome
Copy and PasteSpecial as text I got these results:

Sheet5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 169px"><COL style="WIDTH: 86px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Bournemouth v Exeter</TD><TD>Sat 4 Feb</TD><TD style="TEXT-ALIGN: right">15:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Show</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Bury v Hartlepool</TD><TD>Sat 4 Feb</TD><TD style="TEXT-ALIGN: right">15:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Show</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Carlisle v Chesterfield</TD><TD>Sat 4 Feb</TD><TD style="TEXT-ALIGN: right">15:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Show</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Charlton v Rochdale</TD><TD>Sat 4 Feb</TD><TD style="TEXT-ALIGN: right">15:00</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[s0up2up]

From testing it seems that it very much depends on browser. Which browser are you using?

 

[bertie]

I tested using Google Chrome (post#2 sample).
And IE8 - this produced a mess.

 

[s0up2up]

Yeah i am currently running it through Firefox and IE8, being at work and all. Both create a mere... It could be done, but would be easier if he was using Chrome... Hint Hint haha

 

[s0up2up]

Well if he was using Chrome, here is the code, if not place a comment and I can modify it some.

Sub Reformat1()
 

 '"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
 'Make sure that you use this Macro only on the sheet
 'that you are importing your data into.
 '"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
 
 If Not Range("A:A").Find("show", , xlValues, xlWhole, , , False) Is Nothing Then
    
        Application.ScreenUpdating = False
        
        Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter _
            Field:=1, Criteria1:="show"
            
        Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        
        ActiveSheet.AutoFilterMode = False
        
        Application.ScreenUpdating = True
    
    End If
    
End Sub

 

[s0up2up]

Also thank AlphaFrog from this forum for most of that code.

 

[tommyleinen]

Sorry for the lack of response from me, I've been snowed under with other work, but kudos to you both for bettering on with getting a fix for this.

I almost always use firefox, though I also have chrome I can use so this should be fine, I detest IE so that would be a no go anyway!

I will download the excel posting tool you used and show you the format it needs to be rather than describe it (haven't tried the code yet but will do after this post).

Thanks again guys

 

[tommyleinen]

I cannot for some reason istall excel-jeanie, even when as administrator so will try IE copy paste... Here is an example of how the fixtures should be arranged:

<TABLE style="WIDTH: 176pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=235><COLGROUP><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8594" width=235><TBODY><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 176pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=23 width=235>Tuesday, 24 January 2012</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Notts County v Preston, 19:45</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Scunthorpe v Sheffield Wed, 19:45</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Stevenage v MK Dons, 19:45</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23></TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23></TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=23>Friday, 27 January 2012</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Chesterfield v Bournemouth, 19:45</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23></TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23></TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 height=23>Saturday, 28 January 2012</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Brentford v Wycombe, 15:00</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Exeter v Charlton, 15:00</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Hartlepool v Carlisle, 15:00</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Leyton Orient v Colchester, 15:00</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Rochdale v Bury, 15:00</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Tranmere v Huddersfield, 15:00</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=23>Yeovil v Preston, 15:00</TD></TR></TBODY></TABLE>


The months have been shortened to the first 3 letters now but I should be able to get the year and kick off time added. After each day's fixture there is a gap of 2 cells, then the day-date-month-year, then the fixture list.

I like the code you did for Chrome, and would like to adapt it to format to the above. I think we might be able to do this

 

[tommyleinen]

In the code above, where show is present in the line is it possible to modify it to delete just the cells in that row from A:D? This would leave me room on the right hand side for a formula to format as above.