My answer is simple but you need to be careful...and if
you want the workbook that I used, please email me.
The idea is to collect the instances of the courses plus
the start date and a counter. We use a scripting
dictionary to save these details.... a scripting
dictionary works like a collection except that you can
check if an item is already in the dictionary...thus we
can collect unique items.
so to work.
First, add a CLASS module, leave the default name as
Class1 and add this code:-
Option Explicit
Public StarDate As Date
Public CourseCode As String
Public Duration As Long
Next, add a standard module and add this code:-
Sub TransPoseCourse()
Dim dCourses As New Scripting.Dictionary
Dim rSourceTable As Range
Dim rCol As Range
Dim rCell As Range
Dim sCourse As String
Dim course As Class1
Dim dDate As Date
Set rSourceTable = Range("KeyTable")
For Each rCol In rSourceTable.Columns
For Each rCell In rCol.Cells
dDate = 0
sCourse = ""
If rCell.Row = rSourceTable.Row Then
' its the date
dDate = rCell.Value
Else
sCourse = rCell.Value
End If
If sCourse <> "" Then
If dCourses.Exists(sCourse) Then
Set course = dCourses(sCourse)
Else
Set course = New Class1
course.CourseCode = sCourse
course.StarDate = dDate
dCourses.Add sCourse, course
End If
course.Duration = course.Duration + 1
End If
Next
Next
Dim rOutput As Range, index As Long
Set rOutput = _
rSourceTable.Offset(rSourceTable.Rows.Count + 5, _
0).Resize(1, 1)
For index = 0 To dCourses.Count - 1
Set course = dCourses.Items(index)
rOutput.Value = course.StarDate
rOutput.Offset(0, 1) = course.CourseCode
rOutput.Offset(0, 2) = course.Duration
rOutput.Offset(0, 2).NumberFormat = "0"
Set rOutput = rOutput.Offset(1, 0)
Next
End Sub
I placed your data n a sheet starting at B6 then range
named the table 'KeyTable'
The procedure reads each column of the table.
It then loops through each cell in that column. If the
cell is in row 1 of the table, then we know its a date,
otherwaise, if the cell velue isn't "" then we check if
it exists in our cllection. If it isn't we add it, then
increment the counter.
We do this for each column.
After building the data dictionary, we simply drop the
collected data into a table below our source.
A dictionary allows one to store objects as well as
values. This is a demo of how to save useful data in a
class and save that to the dictionary,
Patrick Molloy
Microsoft Excel MVP
