Transform table

Please help me with vb or macros to tranpose Table 1 to Table 2 for any
number of entries. The courses in a row should be made into only one course
in a row as shown in Table 2.

Table 1
Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05
Trainer1 Room1 CSB00 CSB00 CSB00
Trainer2 Room2 CSB01 CSB01

Table 2
Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05
Trainer1 Room1 CSB00
Trainer1 Room1 CSB00
Trainer1 Room1 CSB00
Trainer2 Room2 CSB01
Trainer2 Room2 CSB01


--
JA

One way:

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iRow As Long
Dim iCol As Long
Dim oRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

curWks.Rows(1).Copy _
Destination:=newWks.Range("a1")

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 3
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

oRow = 1

For iRow = FirstRow To LastRow
For iCol = FirstCol To LastCol
If Trim(.Cells(iRow, iCol).Value) = "" Then
'skip it, do nothing
Else
oRow = oRow + 1
.Cells(iRow, 1).Resize(1, 2).Copy _
Destination:=newWks.Cells(oRow, 1)
newWks.Cells(oRow, iCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol
Next iRow
End With

newWks.UsedRange.Columns.AutoFit

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

asante_za wrote:
>
> Please help me with vb or macros to tranpose Table 1 to Table 2 for any
> number of entries. The courses in a row should be made into only one course
> in a row as shown in Table 2.
>
> Table 1
> Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05
> Trainer1 Room1 CSB00 CSB00 CSB00
> Trainer2 Room2 CSB01 CSB01
>
> Table 2
> Trainer Room 25-Jun-05 26-Jun-05 27-Jun-05 28-Jun-05 29-Jun-05
> Trainer1 Room1 CSB00
> Trainer1 Room1 CSB00
> Trainer1 Room1 CSB00
> Trainer2 Room2 CSB01
> Trainer2 Room2 CSB01
>
>
> --
> JA

--

Dave Peterson