Paste every 7 cell (Excel Xp; WIN XPSP2)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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).

Reply | Quote

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

Reply | Quote