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?
0
Wee416 (1)
11/6/2005 11:55:20 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
773 Views

Similar Articles

[PageSpeed] 8

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 Wee@discussions.microsoft.com> wrote in message 
news:3CF19EC7-B8DD-4221-A207-C9C0D701B046@microsoft.com...
>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? 


0
reply6075 (299)
11/7/2005 12:49:28 AM
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 Wee@discussions.microsoft.com> wrote in message
news:3CF19EC7-B8DD-4221-A207-C9C0D701B046@microsoft.com...
> 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?


0
demechanik (4694)
11/7/2005 2:39:37 AM
> 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
--


0
demechanik (4694)
11/8/2005 4:13:55 AM
Reply:

Similar Artilces:

Re-loading Office 2007 under the 2nd user without using 3rd user
My computer hard drive died and had to be replaced. I lost everything, and had to restore back with the original restore disks, losing the 2nd user of a 3 user Office Home & Student 2007 Version. I am trying to find out how I reinstall the 2nd user, without using the 3rd user on the set. Can anyone help me out here? "Lisa B" <Lisa B@discussions.microsoft.com> wrote in message news:0FA6E3FE-AB25-4013-8595-EB71B475662D@microsoft.com... > My computer hard drive died and had to be replaced. I lost everything, and > had to restore back with the original...

Column Titles
I am trying to figure out how to make my column names appear on each page. I have tried the help function and learned the following............. To print row labels on every page, under Print titles in the Columns to repeat at left box, enter the columns that contain the row labels, and then click Print. Based on these instructions and with my column titles appearing in row 1, I am entering 1 and getting error message. What am I doing wrong ? Also, I understand that there is a way to "freeze" the column titles so that they move down on the screen while you are working s...

Count of values in a column based on values in another column
How do i count values based on values in another column. For example Col1 Col2 A 10 A A 20 B 12 B 15 B I want a formula for counting values in col2 where Col1 is "A". So the answer i am looking for is 2 Thanks Hi try =SUMPRODUCT((A2:A7="A")*(B2:B7<>"")) Regards JulieD "student" <agarwalp@lucent.com> wrote in message news:O$i5%23CfgEHA.3632@TK2MSFTNGP09.phx.gbl... > How do i count values based on values in another column. > > For example > > Col1 Col2 > A 1...

Counting columns of dates
I want to create a column which will give me a total of the number o columns which contain a date. Therefore count the columns not the dat in the cells. Any ideas -- mango ----------------------------------------------------------------------- mango7's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2952 View this thread: http://www.excelforum.com/showthread.php?threadid=49229 If all of your "data" is TEXT and/or numbers below 30000, then this might do it for you.......... =COUNTIF(A3:F3,">30000") Vaya con Dios, Chuck, CABGx3 "m...

Database Diagram 15 ruled column limit?
I am creating a database model, before we build the database. This means that I cannot have Visio build this for me. I am using column shapes with connectors and just setting everything up. It has been going fine, but I have 1 issue. I searched for table and then for column. A shape called 15 ruled column is what I found. This works fine unless I have a table with more than 15 columns. Then it doesn't frame my field list after that. Any suggestions out there? I am frustrate that I can't find another shape, change this one to work or something. I have so much of this done, but ...

Have different row heights between columns
What do you have to do to have different row heights at different places in a spread sheet. Can you have adjacent columns with different row heights? AFAIK, it cant be done -- Message posted from http://www.ExcelForum.com Hi not possible -- Regards Frank Kabel Frankfurt, Germany reinhold wrote: > What do you have to do to have different row heights at different > places in a spread sheet. Can you have adjacent columns with > different row heights? You could merge cells to give the appearance of different rowheights. Merge A1:A3 and it'll look like A1 is the same height...

determine mail flow problem!!!!!!!!
Help! :-( Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the outbound queue (which is set to always run delivery). Messages stay in there from 10 to 120 minutes before being sent to our gateway MTA and then sent off site (to hotmail.com for example). However when I telnet from a backend server to the gateway over port 25 and send a test message it goes right away. I can't for the life of me figure out why. One example when viewed in message tracking history says: 10:16am - smtp: store driver, message submitted from store 10:16am - smtp: message submitted to ...

GetSaveAsFilename
When a user closes a workbook I want my macro to retrieve the username logged in to the machine and use that as the filename. Is this possible? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ In a general module: Option Explicit Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function fOSUserName() As String ' Returns the network login name Dim lngLen As L...

Using Min to Calculate across a table
I am attempting to massage data so I can see what the maximum number of pieces I can produce based off of various components. My query to figure out how many of each component is available per unit is complete and the output is: Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4 Does anyone have any suggestions as to how I could find the MIN of the components and group by part number? Cheers See the MinOfList() function here: http://allenbrowne.com/func-09.html A better solution would be to create a related table with many records for the combinations that are va...

How to easily collapse columns?
Hi, Is there a way to hide and unhide columns easily without using the menus. I have a large spreadsheet and I would like to navigate it more easily. Any help appreciated Thanks G you might adapt this to your needs. You need to create a button and name it "toggleit" Sub HideG_UnhideG() If Columns("c:j").EntireColumn.Hidden = True Then Columns("c:j").EntireColumn.Hidden = False ActiveSheet.Shapes("toggleit").TextFrame.Characters.Text = _ "HIDE" Else Columns("c:j").EntireColumn.Hidden = True ActiveSheet.Sh...

Creating a Chart based on Values from One Column
Hi There, I have Office 2007. I wish to create a chart (2-D is fine) with values from one column. For example one column has 40 "Yes" and 10 "No" values. How would I represent this in a chart. I tried selecting the column and then clicking on "Insert - Chart" but it does not seem to work. I know this would be easy but I can't figure it out. Would someone help? Regards, Ram Hi. Figured this out. Regards, Ram. "rampiranha" wrote: > Hi There, > > I have Office 2007. I wish to create a chart (2-D is fine) with values from > one c...

Formula to count every other column (dynamic range)
Hello, I wrote yesterday regarding how to setup a formula to count information which will be added on a regular basis. Pecoflyer mentioned dynamic range. Sounds great. I checked it out but my question is how would I do this when I only want to count every other cell because one answer will be yes and one will be no. I want a formula to add up the no and yes in a row on a questionnaire. Would appreciate help/clarification/direction. Thank you. Could you post a small sample of your data, please? -- Pecoflyer Cheers ----------------------------------------------------------------...

I want to delete duplicate rows of similar info and use the lates.
I am trying to paste new data into a workbook that may already contain the it. I need to filter out the old data and insert or keep the new. The new data is not in the same order as the old. Is there a filter or something that will find a duplicate and give me the option of which row I want to delete? Add the new data to the old data, and go to data/filter/advanced filter and select "unique records only". This will show you unique records of both the old and new data. Then copy these unique records into a new file. Hope this helped. >-----Original Message----- >I am try...

columns unhiding
Hidden colmuns are unhiding, sometimes, when workbook is opened. Workbook is: shared, on NT network, no macros or VB code, Transition Navigation Keys is unchecked. Manually confirmed other users not changing format. Changed column width to .01 instead of hiding column. Columns expand to "default" width on opening, sometimes. Have rebuilt the workbook twice. Same problems with the new workbooks. Are you running xl97? Some posters say that upgrading to SR2 helps. But others have said that it had no effect. If you're not running xl97SR2, you may want to try it. TomC ...

Weed out words using Excel??
Is there a way to automatically isolate an appearance of a word in list of words, eg I have a full dictionary of words, and I want to end up wit a list where the word (for example) "log" appears. So if the big lis contains: logarithm chair table catalogue (each on a sepearte line) The list I would want would be: logarithm catalogue (leaving out the others) Thanks -- Message posted from http://www.ExcelForum.com Hi Have a look at Data / Filter / Autofilter. Andy. PS I'd love to know where you got your text file from!! "sensible >" <<sensible.148ne7@exc...

Internet Explorer (Browser) Crashes when using CRM Menu Bar
Problem: A user clicks on a menu bar item within CRM and Internet Explorer crashes. The user is asked if they wish to send the error report to Microsoft and closes out the current IE window. (Obviously this is very annoying when attempting to click on "Tools" > "Advance Find..."). This behavior appears to happen on the client machines that have been tested. When trying to duplicate this issue, I have found that on the initial start-up of CRM the menu system works fine. However, after navigating around in CRM (e.g. moving from the initial start-up screen to W...

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

Referencing previous column in COUNTIF
Evening All, I am a novice Excel user, so please excuse the probably very basic question. I have a spreadsheet with two columns, e.g. A E D E F L A E G E I am trying to get some totals for column 2 based on the value, so I used COUNTIF(D4:D34, "E"). This is great, but I only want to count column 2 if the corresponding cell in column 1 is "A" (or not "A" for my other total). I think I want to somehow use INDIRECT as well, but can't quite figure out how! The total I am looking for in the example would be 2 (col2="E" and col1="...

Using HitTesting CListView
Hi, I have a CListCtrl which I have used to emulate a grid. I have several columns and rows but I need to be able to click on any particular row and column and find out which row and column I have selected. I have tried to use the following but doesn't seem to work void CListViewView::OnDblclkList(NMHDR* pNMHDR, LRESULT* pResult) { // TODO: Add your control notification handler code here DWORD dwPos = ::GetMessagePos (); CPoint point ((int) LOWORD (dwPos), (int) HIWORD (dwPos)); m_List.ScreenToClient(&point); int nIndex; if ((nIndex = m_List.HitTest(point)) != -1)...

convert columns to rows & rows to columns
Help! I need to change the format of sheet3 of a workbook, making th rows into columns and the columns into rows. This worksheet has formul links with the other sheets in that workbook, as well as formula link in other workbooks. How can this be done and still maintain th integrity of the information being imported automatically from th other sheets and workbooks? Can it be done -- ROCKWARRIO ----------------------------------------------------------------------- ROCKWARRIOR's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2709 View this thread: http://www.excelf...

Message sorting after using the find feature.
When Outlook is sorted by received (up) so that the newest messages appear at the bottom of the screen -&- you search using the find toolbar -then view your results -then click the X to close the find toolbar, the view will revert to and highlight the oldest message rather than the newest that was highlighted prior to the find function. This is not the way that Word97 worked and was curious if anyone else had noticed this and/or found a solution. Thanks. ...

Dynamically determining when a month ends
Hello all, Ok, I have a simple excel spreadsheet, where I keep track of my spending on one tab, my income in another and my summary in a thrid. My problem happens when I try to dynamically calc. avg's for months in the summary page. For instance, if I want to see how much money I spent on average per month, or even per day, I draw from the data entered into the spending page. Now, the spending page is set up with the following columns: Date:: Description:: Amount The problem with this is that I can't predefine a max row # for each month, since I might have 80 entries in spending...

how to determine the size of the sheet
Hi I had a collegue who told me once a way to dtermine the number of the rows in a sheet of the workbook I am working at. It is known that when you open a new workbook each sheet has maybe 65365 or something like that rows. MY QUESTION IS: HOW TO MAKE THE SHEET 1000 ROWS OR WHATEVER NUMBERS OF ROWS I ONLY NEED? Thanks in Advance, Ahmed Hi Ahmed The number of rows and columns are fixed. Your workaround would be to hide the ones you consider unneeded. HTH. Best wishes Harald "Ahmed SHEBL" <ahmad.shebl@hotmail.com> skrev i melding news:%231uFO3IcHHA.4720@TK2MSFTNGP0...

Min Max Columns
I have a pivot table that is laid out like this. Month 1 2 3 4 5... 12 L R L R L R L R L R L R 1 _,_ 3,3 _,_ _,_ 2,2 _,_ 2 3,3 _,_ _,_ _,_ 2,2 _,_ 3 _,_ _,_ 3,3 _,_ _,_ 2,2 4 _,_ _,_ _,_ 3,3 3,3 _,_ For each month there are two readings a left and right. I need to find the first left and subtract the last left in each row and correspondingly on the right. The first and last reading can be in any month. I also need to capture the numbers of months between readings to calculate a wear rate per month. The _,_ indicates that no reading was taken that month. On Dec 12, 12:43 pm, dmos...@adelph...

How do you add additional columns?
I would like to add additional columns to my Windows Media Player so I can better classify the music. How do you add additional columns? Dan wrote: > How do you add additional columns? Start by right clicking (or left, if you're left-handed) on any existing header (not the blank space where you might want it to go — that's *my* mistake) -- Steve Swift http://www.swiftys.org.uk/swifty.html http://www.ringers.org.uk ...