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

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 3 Step -1

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i + 1, "A").Value = .Cells(i, j).Value
.Cells(i + 1, "B").Value = .Cells(1, j).Value
.Cells(i + 1, "C").Value = .Cells(2, j).Value
.Cells(i, j).Value = ""
Next j
.Cells(i, "B").Value = .Cells(1, j).Value
.Cells(i, "C").Value = .Cells(2, j).Value
Next i
.Rows("1:2").Delete
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)