Please help with macros to transpose Table 1 to Table 2.

Table 1
05/08/04 06/08/04 07/08/04 08/08/04
A A
B
C C C
D D D
E E

Table 2
Start_Date Course Duration
05/08/04 A 2
06/08/04 B 1
06/08/04 C 3
05/08/04 D 3
06/08/04 E 2

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





-----Original Message-----
Please help with macros to transpose Table 1 to Table 2.

Table 1
05/08/04 06/08/04 07/08/04 08/08/04
A A
B
C C C
D D D
E E

Table 2
Start_Date Course Duration
05/08/04 A 2
06/08/04 B 1
06/08/04 C 3
05/08/04 D 3
06/08/04 E 2

.

Compile error encountered at line:

Dim dCourses As New Scripting.Dictionary


-----Original Message-----
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





-----Original Message-----
Please help with macros to transpose Table 1 to Table 2.

Table 1
05/08/04 06/08/04 07/08/04 08/08/04
A A
B
C C C
D D D
E E

Table 2
Start_Date Course Duration
05/08/04 A 2
06/08/04 B 1
06/08/04 C 3
05/08/04 D 3
06/08/04 E 2

.

.

Hi,

Set a reference to Microsoft Scripting Runtime.

Go to the VBE, Tools | References | scroll down to Microsoft Scripting
Runtime.
Check the checkbox and the click OK.

---
Regards,
Norman


"JA" wrote in message
...
Compile error encountered at line:

Dim dCourses As New Scripting.Dictionary


-----Original Message-----
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





-----Original Message-----
Please help with macros to transpose Table 1 to Table 2.

Table 1
05/08/04 06/08/04 07/08/04 08/08/04
A A
B
C C C
D D D
E E

Table 2
Start_Date Course Duration
05/08/04 A 2
06/08/04 B 1
06/08/04 C 3
05/08/04 D 3
06/08/04 E 2

.

.

Thanks for the advice.
Unfortunately, the output does not return the correct
date. It only returns 12:00:00 AM
Anything else I should do?


-----Original Message-----
Hi,

Set a reference to Microsoft Scripting Runtime.

Go to the VBE, Tools | References | scroll down to
Microsoft Scripting
Runtime.
Check the checkbox and the click OK.

---
Regards,
Norman


"JA" wrote in
message
...
Compile error encountered at line:

Dim dCourses As New Scripting.Dictionary


-----Original Message-----
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





-----Original Message-----
Please help with macros to transpose Table 1 to Table
2.

Table 1
05/08/04 06/08/04 07/08/04 08/08/04
A A
B
C C C
D D D
E E

Table 2
Start_Date Course Duration
05/08/04 A 2
06/08/04 B 1
06/08/04 C 3
05/08/04 D 3
06/08/04 E 2

.

.



.

Please send me the workbook.
My email:


-----Original Message-----
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





-----Original Message-----
Please help with macros to transpose Table 1 to Table 2.

Table 1
05/08/04 06/08/04 07/08/04 08/08/04
A A
B
C C C
D D D
E E

Table 2
Start_Date Course Duration
05/08/04 A 2
06/08/04 B 1
06/08/04 C 3
05/08/04 D 3
06/08/04 E 2

.

.