+ Reply to Thread
Results 1 to 4 of 4

copying values and pasting the same value on multiple cells

  1. #1
    Registered User
    Join Date
    08-21-2007
    Posts
    2

    copying values and pasting the same value on multiple cells

    im having a hard time with this one. im trying to copy data from one sheet to another. what i want to happen is that i paste 7 copies of that data on the second sheet. i have a lot of data to work with and paste 7 times in a different sheet. i was just wondering if this could be done by using a formula.

    to get a better visual on what i want to happen -

    SHEET 1

    Column A
    1
    2
    3

    SHEET 2

    Column A
    1
    1
    1
    1
    1
    1
    1
    2
    2
    2
    2
    2
    2
    2
    3
    3
    3
    3
    3
    3
    3

    I hope you can help me with this one.

    Thanks!
    Last edited by okidonkey; 08-21-2007 at 04:02 AM.

  2. #2
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Copy data from sheet1 to sheet2
    In sheet2 col B put serial numbers against your data in col A (i-e 1, 2, 3... downward).

    copy serial # in col B and paste 7 times in col B at first blank cell. e.g if you have data in col A in range A1:A50, put serial # in col B in range B1:B50 then copy B1:B50 and paste in B51 then in B101 and onward for 7 times.

    Select both columns A & B till last serial # in col B then go to Data > Sort > and sort Ascending on col B. (you will see 7 blank cells in col A under each value of data).

    Now select col A till last value plus 7 cells, press F5 and hit Special tab and select Blanks from options and press Ok (all blank cells will be selected).
    Press = and then hit up arrow only once and press Ctrl+Enter.

    Copy col A and right click > Paste special, check Values, press Ok.
    Delete col B.

    Hope this will do what you require.

  3. #3
    Registered User
    Join Date
    08-21-2007
    Posts
    2
    Quote Originally Posted by starguy
    Copy data from sheet1 to sheet2
    In sheet2 col B put serial numbers against your data in col A (i-e 1, 2, 3... downward).

    copy serial # in col B and paste 7 times in col B at first blank cell. e.g if you have data in col A in range A1:A50, put serial # in col B in range B1:B50 then copy B1:B50 and paste in B51 then in B101 and onward for 7 times.

    Select both columns A & B till last serial # in col B then go to Data > Sort > and sort Ascending on col B. (you will see 7 blank cells in col A under each value of data).

    Now select col A till last value plus 7 cells, press F5 and hit Special tab and select Blanks from options and press Ok (all blank cells will be selected).
    Press = and then hit up arrow only once and press Ctrl+Enter.

    Copy col A and right click > Paste special, check Values, press Ok.
    Delete col B.

    Hope this will do what you require.
    Hi Starguy,

    Thanks a lot!! :-) it worked!

  4. #4
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by okidonkey
    Hi Starguy,

    Thanks a lot!! :-) it worked!
    Good to see that it worked and your problem is solved.

+ 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