Hi,
I have a list of 40,000 football matches played. Data is in the format of
11:45 +00:00, August 19, 2006
Sheffield United v Liverpool - Barclays Premier League - ESPN Soccernet
Score : 1 - 1
Goals scored by Sheffield United : 1
Time : 46',
Goals scored by Liverpool : 1
Time : pen 70',
Yellow Cards : 1 - 2
Yellow Cards time : 37', 64', 82',
Red Cards : 0 - 0
Red Cards time :
********************
The cell count starts with A1 and goes on till the end of the sheet. The data is in a standard format. Data is present in the 1st 11 cells i.e A1:A11 which is then seperated by a "********************" followed by 5 blank cells and followed by the data for the next match. I have the paste the values into the next sheet with the headers
(Date, Team, Score, Home Goals, Time of Home Goals, Away Goals, Time of Away Goals, Yellow Cards, Yellow Cards time, Red Cards, Red Cards Time)
which is 11 row heads followed by 40000 match details.
Any sugesstions. I am not a macro person so I might find this quite challenging.
Last edited by jay36429; 11-07-2011 at 06:26 AM. Reason: Solved
Please post a sample workbook with, say, the details of the first dozen matches or so.
Oh, and an indication of what you would hope/expect to get from that data.
Regards
The data is in the below format starting with cell A1
11:45 +00:00, August 16, 2008
Arsenal v West Bromwich Albion - Barclays Premier League - ESPN Soccernet
Score : 1 - 0
Goals scored by Arsenal : 1
Time : 4',
Goals scored by West Bromwich Albion : 0
Time :
Yellow Cards : 0 - 0
Yellow Cards time :
Red Cards : 0 - 0
Red Cards time :
********************
14:00 +00:00, August 16, 2008
Bolton Wanderers v Stoke City - Barclays Premier League - ESPN Soccernet
Score : 3 - 1
Goals scored by Bolton Wanderers : 3
Time : 34', 41', 45',
Goals scored by Stoke City : 1
Time : 90',
Yellow Cards : 1 - 2
Yellow Cards time : 61', 63', 90',
Red Cards : 0 - 0
Red Cards time :
********************
14:00 +00:00, August 16, 2008
Everton v Blackburn Rovers - Barclays Premier League - ESPN Soccernet
Score : 2 - 3
Goals scored by Everton : 2
Time : 45', 64',
Goals scored by Blackburn Rovers : 3
Time : 22', 66', 90',
Yellow Cards : 2 - 2
Yellow Cards time : 75', 90', 45', 59',
Red Cards : 0 - 0
Red Cards time :
********************
14:00 +00:00, August 16, 2008
Hull City v Fulham - Barclays Premier League - ESPN Soccernet
Score : 2 - 1
Goals scored by Hull City : 2
Time : 22', 81',
Goals scored by Fulham : 1
Time : 8',
Yellow Cards : 3 - 0
Yellow Cards time : 28', 43', 90',
Red Cards : 0 - 0
Red Cards time :
********************
14:00 +00:00, August 16, 2008
Middlesbrough v Tottenham Hotspur - Barclays Premier League - ESPN Soccernet
Score : 2 - 1
Goals scored by Middlesbrough : 2
Time : 71', 86',
Goals scored by Tottenham Hotspur : 1
Time : og 90',
Yellow Cards : 1 - 2
Yellow Cards time : 90', 61', 76',
Red Cards : 0 - 0
Red Cards time :
********************
14:00 +00:00, August 16, 2008
West Ham United v Wigan Athletic - Barclays Premier League - ESPN Soccernet
Score : 2 - 1
Goals scored by West Ham United : 2
Time : 3', 10',
Goals scored by Wigan Athletic : 1
Time : 47',
Yellow Cards : 2 - 1
Yellow Cards time : 39', 49', 68',
Red Cards : 0 - 0
Red Cards time :
********************
16:30 +00:00, August 16, 2008
Sunderland v Liverpool - Barclays Premier League - ESPN Soccernet
Score : 0 - 1
Goals scored by Sunderland : 0
Time :
Goals scored by Liverpool : 1
Time : 83',
Yellow Cards : 0 - 2
Yellow Cards time : 56', 90',
Red Cards : 0 - 0
Red Cards time :
********************
12:30 +00:00, August 17, 2008
Chelsea v Portsmouth - Barclays Premier League - ESPN Soccernet
Score : 4 - 0
Goals scored by Chelsea : 4
Time : 12', 26', pen 45', 89',
Goals scored by Portsmouth : 0
Time :
Yellow Cards : 0 - 1
Yellow Cards time : 45',
Red Cards : 0 - 0
Red Cards time :
********************
14:00 +00:00, August 17, 2008
Aston Villa v Manchester City - Barclays Premier League - ESPN Soccernet
Score : 4 - 2
Goals scored by Aston Villa : 4
Time : 47', 69', 74', 76',
Goals scored by Manchester City : 2
Time : pen 64', 89',
Yellow Cards : 0 - 1
Yellow Cards time : 33',
Red Cards : 0 - 0
Red Cards time :
********************
Sorry I didnt find the attachments box in this screen, The sheet 0809 has the details in the raw format. Sheet1 has a sample of the details in the required format
In cell A2, put:
=ROW()-1
In cell B2, put:
=INDEX('0809'!$A:$A,((ROW()-2)*17)+1+(COLUMN()-2))
Drag both formulae down until you start getting zeros in column B.
Then drag column B across.
Regards
Last edited by TMShucks; 11-07-2011 at 06:27 AM. Reason: Correcting formula
Hey that worked like a Gem, Thanks a tonne for your help and your quick reply.
Regards,
Jay
Please note that I have changed the second formula as it was out by 1.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks