Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reorganising data
Hi,
I'd like to reorganise data from a spreadsheet as follows: I have a number of columns with a couple of rows as title and some data underneath each. The data is unique in any one sheet. I'd like to copy the information on a new sheet in a different manner. For example A B C ... title1 title2 title3 ... X Y Z xx yy zz xy yx zx xz yz zy I'm looking to have the information reorganised A B C xx title1 X xy title1 X xz title1 X yy title2 Y yz title2 Y .... I will then sort on col A, etc I am new to the VB semantics and look forward to any pointers. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reorganising data
Assume source data as posted in Sheet1, cols A to C
In Sheet2, In A2: =OFFSET(Sheet1!$A$3,INT((ROWS($1:1)-1)/3),) In B2: =OFFSET(Sheet1!$A$1,,INT((ROWS($1:1)-1)/3),) In C2: =OFFSET(Sheet1!$A$2,,INT((ROWS($1:1)-1)/3),) Select & copy A2:C2 down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "zvonul" wrote in message ... Hi, I'd like to reorganise data from a spreadsheet as follows: I have a number of columns with a couple of rows as title and some data underneath each. The data is unique in any one sheet. I'd like to copy the information on a new sheet in a different manner. For example A B C ... title1 title2 title3 ... X Y Z xx yy zz xy yx zx xz yz zy I'm looking to have the information reorganised A B C xx title1 X xy title1 X xz title1 X yy title2 Y yz title2 Y ... I will then sort on col A, etc I am new to the VB semantics and look forward to any pointers. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reorganising data
Errata ..
In A2 should be: =OFFSET(Sheet1!$A$3,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) Note that I had presumed Sheet1's A3 contains the value: xx which you indicated in your post (I assume there was no blank row below the 2nd header row) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reorganising data
On Mar 11, 12:20 am, "Max" wrote:
Errata .. In A2 should be: =OFFSET(Sheet1!$A$3,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)) Note that I had presumed Sheet1's A3 contains the value: xx which you indicated in your post (I assume there was no blank row below the 2nd header row) -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- Thanks. This works well if I have a fixed number of columns with the same number of records under each. I also want to have the headings in the second sheet but I can figure that out later. However, the number of records in each column is not the same. One column may have (under the headings) 2 records, another 10, and so on. As I see it, I'd probably need to read the data in each column while not blank (and copy it by appending to the last record in the new format), while there are columns. I just don't know enough about what Excel/VB can do and am not familiar with the syntax. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reorganising data
.. the number of records in each column is not the same
I'm not sure whether that kind of scenario could be done via vba. You could always try posting in .programming for responses. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "zvonul" wrote Thanks. This works well if I have a fixed number of columns with the same number of records under each. I also want to have the headings in the second sheet but I can figure that out later. However, the number of records in each column is not the same. One column may have (under the headings) 2 records, another 10, and so on. As I see it, I'd probably need to read the data in each column while not blank (and copy it by appending to the last record in the new format), while there are columns. I just don't know enough about what Excel/VB can do and am not familiar with the syntax. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reorganising Data | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) |