+ Reply to Thread
Results 1 to 5 of 5

duplicate the each row twice in one worksheet

  1. #1
    L.J.
    Guest

    duplicate the each row twice in one worksheet

    I have 89 rows data (first name, last name) in one worksheet, for some
    reason I need to repeat their name 3 times for some reasons.
    Is there any easy way to do so wihtout running copy 3 times of 89 rows.
    Any tip would be much appreciated.

    Regards,
    MH



  2. #2
    Ken Wright
    Guest

    Re: duplicate the each row twice in one worksheet

    Select the whole lot, copy and then paste at the bottom of your data.
    Repeat then sort on name.

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL 97/00/02/03

    ------------------------------*------------------------------*----------------
    It's easier to beg forgiveness than ask permission :-)
    ------------------------------*------------------------------*----------------


    "L.J." <mhgk@excite.com> wrote in message
    news:eskyzZ3%23FHA.3340@TK2MSFTNGP12.phx.gbl...
    >I have 89 rows data (first name, last name) in one worksheet, for some
    > reason I need to repeat their name 3 times for some reasons.
    > Is there any easy way to do so wihtout running copy 3 times of 89 rows.
    > Any tip would be much appreciated.
    >
    > Regards,
    > MH
    >
    >




  3. #3
    Max
    Guest

    Re: duplicate the each row twice in one worksheet

    Another play to try, which auto-triplicates lines
    in exactly the same order as the source data ..

    Assume source data is in Sheet1,
    cols A and B, from row1 down

    In a new Sheet2,

    Put in A1: =OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/3),)
    Put in B1: =OFFSET(Sheet1!$B$1,INT((ROWS($A$1:A1)-1)/3),)

    Select A1:B1,
    fill down by 3 times as many rows as there is data in Sheet1
    (e.g.: since you have 89 lines in Sheet1, fill down to B267)

    Or, we could simply overfill to say, B300,
    to dynamically cater for new data additions in Sheet1
    (It'll appear as zeros once the existing data in Sheet1 is exhausted)

    If required, kill the formulas in cols A & B with an in-place:
    Copy > Paste special > check "Values" > OK
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "L.J." <mhgk@excite.com> wrote in message
    news:eskyzZ3#FHA.3340@TK2MSFTNGP12.phx.gbl...
    > I have 89 rows data (first name, last name) in one worksheet, for some
    > reason I need to repeat their name 3 times for some reasons.
    > Is there any easy way to do so wihtout running copy 3 times of 89 rows.
    > Any tip would be much appreciated.
    >
    > Regards,
    > MH
    >
    >




  4. #4
    Max
    Guest

    Re: duplicate the each row twice in one worksheet

    > In a new Sheet2,
    > Put in A1: =OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/3),)
    > Put in B1: =OFFSET(Sheet1!$B$1,INT((ROWS($A$1:A1)-1)/3),)


    The above "starting cell" formulas can be placed in any 2 starting cells
    (next to each other) in the new sheet, not necessarily in A1 & B1, and
    filled down from there.

    Adjust the number "3" within the part: .. INT((ROWS($A$1:A1)-1)/3)
    to suit as required. Eg: If we want each line to repeat 4 times, change the
    "3" to "4", then just fill down to cover at least 4 x the number of lines in
    the source data. And so on.
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  5. #5
    Ken Wright
    Guest

    Re: duplicate the each row twice in one worksheet

    And if you wanted it all to stay in the same order, then simply add another
    column to your data, fill down with 1,2,3,4,5 etc Copy all, paste under and
    then repeat and then sort on the number column. You can then delete it if
    you want.

    Regards
    Ken.....................


    "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
    news:O5$ARA4%23FHA.2708@TK2MSFTNGP12.phx.gbl...
    > Select the whole lot, copy and then paste at the bottom of your data.
    > Repeat then sort on name.
    >
    > --
    > Regards
    > Ken....................... Microsoft MVP - Excel
    > Sys Spec - Win XP Pro / XL 97/00/02/03
    >
    > ------------------------------*------------------------------*----------------
    > It's easier to beg forgiveness than ask permission :-)
    > ------------------------------*------------------------------*----------------
    >
    >
    > "L.J." <mhgk@excite.com> wrote in message
    > news:eskyzZ3%23FHA.3340@TK2MSFTNGP12.phx.gbl...
    >>I have 89 rows data (first name, last name) in one worksheet, for some
    >> reason I need to repeat their name 3 times for some reasons.
    >> Is there any easy way to do so wihtout running copy 3 times of 89 rows.
    >> Any tip would be much appreciated.
    >>
    >> Regards,
    >> MH
    >>
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1