Try this code. Change source and dest sheet names as required.


Sub SortByRooms()
Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")


With DestSht
.Range("A1") = "Room"
For Period = 1 To 8
.Cells(1, Period + 1) = "P" & Period
Next Period
End With

NewRow = 2
RowCount = 2

With SourceSht
Do While .Range("A" & RowCount) < ""
Room = .Range("A" & RowCount)
Period = .Range("B" & RowCount)
Course = .Range("F" & RowCount)

With DestSht
'check if room already exists
Set c = .Columns("A").Find(what:=Room, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = Room
.Cells(NewRow, Period + 1) = Course
NewRow = NewRow + 1
Else
'check if room already assigned
If .Cells(c.Row, Period + 1) < "" Then
MsgBox ("All ready in use Room : " & Room & " , Period: " &
Period)
Else
.Cells(c.Row, Period + 1) = Course
End If

End If

End With
RowCount = RowCount + 1
Loop
End With

End Sub


"Jcraig713" wrote:

Hello. I have source data numbering about 2500 records as shown below:

A B C D E F G
Rm Period Term crscode sec course teacher
A1 2 HS1 HSPE110 1 Health Koenings
A1 3 HS1 HSPE110 2 Health Koenings
A1 4 HS1 HSPE110 3 Health Koenings
A1 6 HS1 HSPE110 4 Health Koenings
A1 7 HS1 HSPE110 5 Health Koenings
A2 2 HS1 HSSP181 1 Geography Moriconi
A2 3 HS1 HSSP220 1 English10 S1 Moriconi
A2 7 HS1 HSSP380 1 History S1 Moriconi
A3 2 HS1 HSCT100 2 Business Morton
A3 5 HS1 HSCT100 4 Business Morton
A3 6 HS1 HSCT210 1 Busin Mgmt Morton
A3 7 HS1 HSCT100 5 Business Morton

I need to re-order the data so the data shows like the following; one room
to many classes in periods on one record line:

Room P1 P2 P3 P4 P5 P6 P7
P8
A1 Health Health Health Health Health
A2 Geog Engl10 Hist
A3 Busin Busin BusMgt Busin

Any help would be greatly appreciated.