Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Paste every 7 cell (Excel Xp; WIN XPSP2)

    I have a worksheet with a large number of columns of monthly data. There are 9 columns of data for each month of the year. For example, the production number for Jan, Feb, Mar, Apr, ....thru Dec, are located in every 9th column [say row 3]. Is there an easy way to copy the values to row 3, in the cell in every 9th column into consecutive cells in say row 400? So my goal is to have row 400, the first 12 columns, be the production amount for each month of the year. I then need to repeat this step in rows 401-408 for the other 8 values recorded on a monthly basis.

    As I said, I have 9 monthly catagories that have monthly data every 9th column and have started to use the copy and paste routine to build a data grid. I have looked through the help, but can't find a way to easily to build a formula to copy a cell in every 9th column in a row to consecutive cells in a another row. I do not believe I can use a pivot table as the cells in the data grid that I am creating in rows 400-408 are referenced in another report and will be updated monthly. That is why I was looking for a way to build the data grid using formulas and thought that there has to be a way to copy every 9th cell in a row to consecutive cells in a new row. THANKS.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Paste every 7 cell (Excel Xp; WIN XPSP2)

    A possible solution.

    Go Down to row 400 assuming that Jan is in Col J put in Cell J400 the formula $J3, repeat this formula in every 9th, ie. in Cell R400 $R3
    Next copy down the formula for all the rows you want.
    In row 399 At the Top Or Each Col use a Sort Key Number 1 = jan; 2 = Feb Ect
    Now beginning in row J399 select the entire range with the formulas. Include the blank Cols.
    Using Data Sort, Select Option and then sort left to right. Make sure that row 399 is the Sort Key row selected.
    Then Sort
    You will have the information in a Grid and all the blank cols will be sorted outside the data.

    Regards,

    Tom Duthie

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste every 7 cell (Excel Xp; WIN XPSP2)

    Let's say that your data begin in cell A3, and that you want to copy starting in cell A400.
    Enter the following formula in A400:

    =OFFSET($A$3,0,(COLUMN()-COLUMN($A$400))*9+ROW()-ROW($A$400))

    Fill right to column L (12 columns), then down to row 408 (9 rows).

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Paste every 7 cell (Excel Xp; WIN XPSP2)

    Hans,
    Thanks....no surprise. It works...take care.
    Jim

Posting Permissions

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