I combined the sheets together so it will be easy to put the data into
the userform. See if you like this format

Sub Combinesheets()

Set StudentNameSht = Sheets("Sheet1")
Set StudentCourseSht = Sheets("sheet2")
Set Destsht = Sheets("sheet3")

With StudentCourseSht
'sort sheet by Student ID and Quarter
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending, _
key2:=.Range("C1"), _
order2:=xlAscending
End With

With StudentNameSht
'copy header row to dest sht
Rows(1).Copy _
Destination:=Destsht.Rows(1)

RowCount1 = 2
DestRowCount = 2
Do While .Range("A" & RowCount1) < ""
StudentID = .Range("A" & RowCount1)
Set StudentIDRange = .Range("A" & RowCount1 & ":C" & RowCount1)

ColCount = 5
'find student course records
With StudentCourseSht
StudentIDRange.Copy _
Destination:=Destsht.Range("A" & DestRowCount)
Set c = .Columns("A").Find(what:=StudentID, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
RowCount2 = c.Row
Quarter = .Range("C" & RowCount2)
Destsht.Range("D" & DestRowCount) = Quarter
Do While .Range("A" & RowCount2) = StudentID
If .Range("C" & RowCount2) < Quarter Then
DestRowCount = DestRowCount + 1
StudentIDRange.Copy _
Destination:=Destsht.Range("A" & DestRowCount)
Destsht.Range("D" & DestRowCount) = Quarter
ColCount = 5
Quarter = .Range("A" & RowCount2)
End If
Course = .Range("B" & RowCount2)
Destsht.Cells(DestRowCount, ColCount) = Course
ColCount = ColCount + 1
RowCount2 = RowCount2 + 1
Loop
End If

End With
DestRowCount = DestRowCount + 1
RowCount1 = RowCount1 + 1
Loop

End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175357

Microsoft Office Help