help converting table structure

[Chris Barnett]

hi - i'm a teacher and am after some help changing the structure of a large
table.

i am given every few weeks an excel table with the structure

Columns: Admin RedMon RedTue RedWed RedThu RedFri BluMon
BluTue BluWed BluThu BluFri

The Admin is a number assigned to each pupil in the school (1400 on role)
In the RedMon, RedTue etc columns is a room number.

I could do with the structure of the table being changed to the following

Admin Cycle (Red/Blue) Day Room


So that each pupil will now have 10 entries (one for each day of the 2 week
cycle)

i hope that this is easy to understand.

This is actually then to be imported into Access for use as part of a
database.

I would be grateful for any help anyone could give me with this.

thanks
Chris Barnett
(e-mail address removed)

 

[Dave Peterson]

I think that this works...

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim iCol 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, 4).Value _
= Array("Admin", "Cycle (Red/Blue)", "Day", "Room")
oRow = 1

With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
For iCol = 2 To 11
If IsEmpty(.Cells(iRow, iCol)) Then
'do nothing
Else
oRow = oRow + 1
newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
newWks.Cells(oRow, "B").Value _
= Left(.Cells(1, iCol).Value, 3)
newWks.Cells(oRow, "C").Value = Mid(.Cells(1, iCol), 4)
newWks.Cells(oRow, "D").Value = .Cells(iRow, iCol).Value
End If
Next iCol
Next iRow
End With

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

 

[Chris Barnett]

just thought i'd let you know that i've used your code and after a minor
tweak and me getting my head round using macros it works a treat.

so thanks very much for you help.

cheers
Chris