Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Determine columns used
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 wrote in message ... 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? |
#3
|
|||
|
|||
Determine columns used
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 wrote in message ... 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? |
#4
|
|||
|
|||
Determine columns used
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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort the data in 8 columns by two of the columns? | Excel Worksheet Functions | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |