+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Excel macro to copy/paste every nth rows

  1. 11-07-2011, 06:04 AM #1
    jay36429
    jay36429 is offline
    Registered User
    Join Date
    10-22-2008
    Location
    Bangalore
    Posts
    13

    Excel macro to copy/paste every nth rows

    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 07:26 AM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 11-07-2011, 06:12 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: Excel macro to copy/paste every nth rows

    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
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 11-07-2011, 06:27 AM #3
    jay36429
    jay36429 is offline
    Registered User
    Join Date
    10-22-2008
    Location
    Bangalore
    Posts
    13

    Re: Excel macro to copy/paste every nth rows

    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 :
    ********************
    Reply With QuoteRegister To Reply
  4. 11-07-2011, 06:30 AM #4
    jay36429
    jay36429 is offline
    Registered User
    Join Date
    10-22-2008
    Location
    Bangalore
    Posts
    13

    Re: Excel macro to copy/paste every nth rows

    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-07-2011, 07:19 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: Excel macro to copy/paste every nth rows

    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 TMS; 11-07-2011 at 07:27 AM. Reason: Correcting formula
    Reply With QuoteRegister To Reply
  6. 11-07-2011, 07:25 AM #6
    jay36429
    jay36429 is offline
    Registered User
    Join Date
    10-22-2008
    Location
    Bangalore
    Posts
    13

    Re: Excel macro to copy/paste every nth rows

    Hey that worked like a Gem, Thanks a tonne for your help and your quick reply.

    Regards,
    Jay
    Reply With QuoteRegister To Reply
  7. 11-07-2011, 07:28 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,413

    Re: Excel macro to copy/paste every nth rows

    Please note that I have changed the second formula as it was out by 1.

    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1