Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transform table
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transform formula | Excel Worksheet Functions | |||
Transform table into calendar format | Excel Discussion (Misc queries) | |||
Transform csv to qif or ofx format? | Excel Discussion (Misc queries) | |||
Table Transform | Excel Discussion (Misc queries) | |||
Transform. | Excel Programming |