columns to rows

[Guest]

I have a file with a great deal of data (though my data will not exced the
limit once arranged in rows) that I need to transpose from column to row
based on column A.

Data is arranged as follows:

ID Day Pct Day Pct Day Pct Day Pct
55462 Monday 45 Tuesday 55
135464 Friday 20 Wednesday 20 Thursday 60
15448 Thurs 20 Monday 20 Friday 20 Wednesday 40
1487 Monday 30 Tuesday 20 0PA 50
My goal:

ID Day Pct
55462 Monday 45
55462 Tuesday 55
135464 Friday 20
135464 Wednesday 20
135464 Thursday 60
15448 Thurs 20
15448 Monday 20
15448 Friday 20
15448 Wednesday 40
1487 Monday 30
1487 Tuesday 20
1487 Saturday 50

Any assistance is appreciated.

 

[Dave Peterson]

One way is to use a macro:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
NewWks.Range("a1").Resize(1, 3).Value _
= Array("ID", "Day", "Pct")
oRow = 1

With CurWks
FirstRow = 2 'headers in 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 2

For iRow = FirstRow To LastRow
LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column
For iCol = FirstCol To LastCol Step 2
oRow = oRow + 1
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Resize(1, 2).Value _
= .Cells(iRow, iCol).Resize(1, 2).Value
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