reorganising data

[zvonul]

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

 

[Max]

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]

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)

 

[zvonul]

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.

 

[Max]

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