Is this possible - plot data to matrix

[crem1002]

I have an Excel file with a sheet that has the following columns: Name, Activity, Start Date,
End Date. What I would like to do: I would like to create summary sheet with a calendar-like
matrix, with the names as rows, and dates as columns. I would then like to plot the activity in
the corresponding dates (based on the start and end date). Note: If there are schedule
conflicts, the conflict should show up in another row
- Is this even possible to do in Excel???
Source:
Name Activity StartDate EndDate
PersonA Vacation Jan1 Jan3
PersonA Office Jan4 Jan5
PersonA Field Jan6 Jan7
PersonB Vacation Jan1 Jan3
PersonB Field Jan4 Jan7
PersonB Office Jan5 Jan5
Summary:
Name Jan1 Jan2 Jan3 Jan4 Jan5 Jan6 Jan7
PersonA Vacation Vacation Vacation Office Office Field Field
PersonB Vacation Vacation Vacation Field Field Field Field
PersonB Office
I would appreciate thoughts / feedback.

 

[theozz]

Hi ! Here Suggest to you
<table style="border-collapse: collapse;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> DataSheet= Sheet1</td></tr><tr><td width="422"><table colspan="9" rowspan="7" bgcolor="#939393" border="0" cellspacing="1" width="452"><col width="30"><col width="63"><col width="58"><col width="59"><col width="58"><col width="46"><col width="46"><col width="46"><col width="46"><tbody bgcolor="#ffffff"><tr height="18"><td> </td><td style="" align="center" bgcolor="#dcdcdc">A</td><td style="" align="center" bgcolor="#dcdcdc">B</td><td style="" align="center" bgcolor="#dcdcdc">C</td><td style="" align="center" bgcolor="#dcdcdc">D</td><td style="" align="center" bgcolor="#dcdcdc">E</td><td style="" align="center" bgcolor="#dcdcdc">F</td><td style="" align="center" bgcolor="#dcdcdc">G</td><td style="" align="center" bgcolor="#dcdcdc">H</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">1 </td><td align="left" bgcolor="#ffffff">Source:</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">2 </td><td align="left" bgcolor="#ffffff">Name</td><td align="left" bgcolor="#ffffff">Activity</td><td align="left" bgcolor="#ffffff">StartDate</td><td align="left" bgcolor="#ffffff">EndDate</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">3 </td><td align="left" bgcolor="#ffffff">PersonA</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">01-Jan</td><td align="left" bgcolor="#ffffff">03-Jan</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">4 </td><td align="left" bgcolor="#ffffff">PersonA</td><td align="left" bgcolor="#ffffff">Office</td><td align="left" bgcolor="#ffffff">04-Jan</td><td align="left" bgcolor="#ffffff">05-Jan</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">5 </td><td align="left" bgcolor="#ffffff">PersonA</td><td align="left" bgcolor="#ffffff">Field</td><td align="left" bgcolor="#ffffff">06-Jan</td><td align="left" bgcolor="#ffffff">07-Jan</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">6 </td><td align="left" bgcolor="#ffffff">PersonB</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">01-Jan</td><td align="left" bgcolor="#ffffff">03-Jan</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">7 </td><td align="left" bgcolor="#ffffff">PersonB</td><td align="left" bgcolor="#ffffff">Field</td><td align="left" bgcolor="#ffffff">04-Jan</td><td align="left" bgcolor="#ffffff">07-Jan</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">8 </td><td align="left" bgcolor="#ffffff">PersonB</td><td align="left" bgcolor="#ffffff">Office</td><td align="left" bgcolor="#ffffff">05-Jan</td><td align="left" bgcolor="#ffffff">05-Jan</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">9 </td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">10 </td><td align="left" bgcolor="#ffffff">Summary:</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">11 </td><td align="left" bgcolor="#ffffff">Name</td><td align="left" bgcolor="#ffffff">01-Jan</td><td align="left" bgcolor="#ffffff">02-Jan</td><td align="left" bgcolor="#ffffff">03-Jan</td><td align="left" bgcolor="#ffffff">04-Jan</td><td align="left" bgcolor="#ffffff">05-Jan</td><td align="left" bgcolor="#ffffff">06-Jan</td><td align="left" bgcolor="#ffffff">07-Jan</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">12 </td><td align="left" bgcolor="#ffffff">PersonA</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Office</td><td align="left" bgcolor="#ffffff">Office</td><td align="left" bgcolor="#ffffff">Field</td><td align="left" bgcolor="#ffffff">Field</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">13 </td><td align="left" bgcolor="#ffffff">PersonB</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Field</td><td align="left" bgcolor="#ffffff">Office</td><td align="left" bgcolor="#ffffff">Field</td><td align="left" bgcolor="#ffffff">Field</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">14 </td><td align="left" bgcolor="#ffffff">PersonB</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Vacation</td><td align="left" bgcolor="#ffffff">Field</td><td align="left" bgcolor="#ffffff">Office</td><td align="left" bgcolor="#ffffff">Field</td><td align="left" bgcolor="#ffffff">Field</td></tr></tbody></table></td></tr></tbody></table>
<colspan=14 width="14" rowspan="3" height="54"></colspan=14><table style="border-collapse: collapse;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="14" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)
If Pink Formula that is FormulaArray !!!
WithOut {} Input Formula and Press Control+Shift+Enter</td></tr><tr height="24"><td align="center" bgcolor="#d3d3d3" width="4%">No</td><td align="center" bgcolor="#d3d3d3" width="9%">Addr'</td><td align="center" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td><td align="center" bgcolor="#d3d3d3" width="15%">Result</td><td align="center" bgcolor="#d3d3d3">Etc</td></tr><tr height="20"><td align="center">1</td><td align="center">B12</td><td>=INDIRECT("b"&MAX(($C$3:$C$8<=B$11)*(B$11<=$D$3:$D$8)*($A$3:$A$8=$A12)*ROW($A$3:$A$8)))</td><td align="left">Vacation</td><td></td></tr><tr height="20"><td align="center">2</td><td align="center"></td><td>B12 His Formula Used This Cell -> B12:H14</td><td align="left"></td><td></td></tr><tr height="24"><td colspan="14"> If this is not wanted answer, give more detail of Questions
if Formula occurs Error! / Different Answer... please Remove the Space.
</td></tr></tbody></table>

 

[jindon]

try

Sub test()
Dim minDate As Date, maxDate As Date
Dim a, b(), x As Long, y As Long, n As Long
Dim i As Long, ii As Long
With Range("a1").CurrentRegion
    a = .Value
    minDate = Application.Min(.Range("c:d"))
    maxDate = Application.Max(.Range("c:d"))
End With
x = DateDiff("d", minDate, maxDate)
n = 1
ReDim b(1 To UBound(a, 1), 1 To x + 2)
b(1, 1) = "Name"
For i = 0 To x
    b(1, i + 2) = DateAdd("d", i, minDate)
Next
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For i = 2 To UBound(a, 1)
        If IsDate(a(i, 3)) Then
            If Not .exists(a(i, 1)) Then
                n = n + 1: b(n, 1) = a(i, 1)
                .Add a(i, 1), n
            End If
            x = a(i, 3) - minDate + 2
            y = x + a(i, 4) - a(i, 3)
            For ii = x To y
                b(.Item(a(i, 1)), ii) = _
                b(.Item(a(i, 1)), ii) & _
                IIf(b(.Item(a(i, 1)), ii) <> "", "/", "") & a(i, 2)
            Next
        End If
    Next
End With
With Range("g1")
    .CurrentRegion.ClearContents
    .Resize(n, UBound(b, 2)).Value = b
    With .Offset(, 1).Resize(, UBound(b, 2))
        .NumberFormat = "d-mmm"
        .EntireColumn.AutoFit
    End With
End With
End Sub

 

[crem1002]

theozz and jindon-

Thank you for the quick response! A bit over my head as I do not know VBA programming so really appreciate the help.

Theozz-

I have yet to try your solution (not really sure how to use it) but am trying to figure it out.

Jindon-

This worked great! However, I ran into an Out of Memory error when I ran against a file with 1024 rows. It does not seem to be a problem with the number of rows but the date range - July 1 to Dec 31... Is there any way around this?

(I'm sorry if I seem dependent-- just new at this...)

Thank you.

 

[jindon]

Out of memory?

July 1 to Dec 31
Only 183 + 1 columns with 1024 rows ?

This may be slower...

Sub test()
Dim minDate As Date, maxDate As Date
Dim a, x As Long, y As Long, n As Long
Dim i As Long, ii As Long, myCell As Range
With Range("a1").CurrentRegion
    a = .Value
    minDate = Application.Min(.Range("c:d"))
    maxDate = Application.Max(.Range("c:d"))
End With
x = DateDiff("d", minDate, maxDate)
Set myCell = Range("f1")
myCell.CurrentRegion.ClearContents
myCell.Value = "Name"
For i = 0 To x
    myCell.Offset(, i + 1).Value = DateAdd("d", i, minDate)
Next
myCell.Offset(, 1).Resize(, x).NumberFormat = "d-mmm"
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For i = 2 To UBound(a, 1)
        If IsDate(a(i, 3)) Then
            If Not .exists(a(i, 1)) Then
                n = n + 1: myCell.Offset(n).Value = a(i, 1)
                .Add a(i, 1), n
            End If
            x = a(i, 3) - minDate + 2
            y = x + a(i, 4) - a(i, 3)
            For ii = x To y
                myCell.Offset(n, ii - 1).Value = _
                myCell.Offset(n, ii - 1).Value & _
                IIf(myCell.Offset(n, ii - 1).Value <> "", "/", "") & a(i, 2)
            Next
        End If
    Next
End With
End Sub

 

[crem1002]

Jindon-

This gives me a "run-time error '1004': application-defined or object-defined error" and highlights the following "myCell.Offset(, i + 1).Value = DateAdd("d", i, minDate)". I am using MS Excel 2003... could that be the prob?

Could I email the data I am working with in case it has somethign to do with the data?

crem1002

 

[jindon]

I can't think of any reason why it raises error on such line...

Do you have any Merged cell?

 

[crem1002]

hello Jindon-

There were no merged cells. I was able to do away with the error by reducing the range. I think the problem is with the limit in the number of columns. I will just plan to limit the date range to 6 months.

ANy chance you can help with a macro that will change the format:
a) take contiguous cells with the same value and merge them into one?
b) apply colors/fill to the cells based on the activity (e.g. Office = Green, Vacation = Red, etc)?

Thank you again Jindon.

 

[jindon]

try this

Sub test()
Dim minDate As Date, maxDate As Date
Dim a, x As Long, y As Long, n As Long
Dim i As Long, ii As Long, myCell As Range
With Range("a1").CurrentRegion
    a = .Value
    minDate = Application.Min(.Range("c:d"))
    maxDate = Application.Max(.Range("c:d"))
End With
x = DateDiff("d", minDate, maxDate)
Set myCell = Range("g1")
myCell.CurrentRegion.ClearContents
myCell.Value = "Name"
For i = 0 To x
    myCell.Offset(0, i + 1).Value = DateAdd("d", i, minDate)
Next
myCell.Offset(, 1).Resize(, x).NumberFormat = "d-mmm"
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For i = 2 To UBound(a, 1)
        If IsDate(a(i, 3)) Then
            If Not .exists(a(i, 1)) Then
                n = n + 1: myCell.Offset(n).Value = a(i, 1)
                .Add a(i, 1), n
            End If
            x = a(i, 3) - minDate + 2
            y = x + a(i, 4) - a(i, 3)
            For ii = x To y
                myCell.Offset(.Item(a(i, 1)), ii - 1).Value = _
                myCell.Offset(.Item(a(i, 1)), ii - 1).Value & _
                IIf(myCell.Offset(.Item(a(i, 1)), ii - 1).Value <> "", "/", "") & a(i, 2)
            Next
        End If
    Next
End With
End Sub

 

[jindon]

hello Jindon-

There were no merged cells. I was able to do away with the error by reducing the range. I think the problem is with the limit in the number of columns. I will just plan to limit the date range to 6 months.

ANy chance you can help with a macro that will change the format:
a) take contiguous cells with the same value and merge them into one?
b) apply colors/fill to the cells based on the activity (e.g. Office = Green, Vacation = Red, etc)?

Thank you again Jindon.

Can you explain a bit more about a) & b) with the sample data?