Determine columns used

I have 100+ spreadsheets which i have to edit into a certain format. The
spreadsheets currently have 100 columns (from 1-100). From there i have to
reduce the number of columns set to 8 columns.

E.g:
1 2 3 4 5 6 7 8
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards, it
goes to the
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1)
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0

How do i do that without altering the order of the value of the cell?

Let me make sure we understand the question.

You have 100 columns in your current worksheet.
How many rows are there in the worksheet?
Does

I1-> A2
J1 ->B2
K1->C2
L1 ->D2
M1->E2
N1-:>F2
O1->G2
p1->H2

etc?

"Jane Wee" <Jane [email protected]> wrote in message
news:[email protected]...
>I have 100+ spreadsheets which i have to edit into a certain format. The
> spreadsheets currently have 100 columns (from 1-100). From there i have to
> reduce the number of columns set to 8 columns.
>
> E.g:
> 1 2 3 4 5 6 7 8
> 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards,
> it
> goes to the
> 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1)
> 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
>
> How do i do that without altering the order of the value of the cell?

Try this on a *spare* copy of your file ..

Assume you have 120 sheets named as :
Sheet1, Sheet2 ... Sheet120
where in each sheet, A1:IV1 contains the data
you want re-formatted (as per post) into A1:H32

Copy this formula below to the clipboard first:

=OFFSET($A$1,,ROWS($A$1:A2)*8-8
+MOD(COLUMNS($A$1:A2)-1,8))

Then select A2 in Sheet1 (the "leftmost" sheet).
Hold down SHIFT, scroll to and select the last sheet
(Sheet120) on the right. This will group all the 120 sheets.

Now do a right-click inside the formula bar > Paste, and press ENTER. This
will paste the formula above into A2 in every sheet

Re-select A2, copy across to H2, fill down to H32
(This propagates the formula in A2
across the range A2:H32 in every sheet)

The formulae will re-arrange what's in I1:IV1
into A2:H32 in every sheet in the zig-zag manner desired.
(A1:H1 is left untouched to form the 1st row of the 32R x 8C grid)

Then right-click on any of the grouped sheets > select "Ungroup Sheets"

Use the sheet grouping-ungrouping steps if you wish to kill all the formulas
in A2:H32 (via an "in-place" copy > paste special > check "Values" > OK),
and clear cells I1:IV1.

Take care to ungroup the sheets immediately
at the end of any sheet-grouping action.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jane Wee" <Jane [email protected]> wrote in message
news:[email protected]...
> I have 100+ spreadsheets which i have to edit into a certain format. The
> spreadsheets currently have 100 columns (from 1-100). From there i have to
> reduce the number of columns set to 8 columns.
>
> E.g:
> 1 2 3 4 5 6 7 8
> 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards,
it
> goes to the
> 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1)
> 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
>
> How do i do that without altering the order of the value of the cell?

> Use the sheet grouping-ungrouping steps if you wish to kill all the
formulas
> in A2:H32 (via an "in-place" copy > paste special > check "Values" > OK),
> and clear cells I1:IV1.

The last line should read as:
> and then clear cells I1:IV1.

The freezing of the results evaluated by the formulae should be done first.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--