building an excel calendar

[bharath235]

Hi Friends,

I have two columns with dates and corresponding text. This needs to be recorded into a calendar (kind of) arrangement, wherein the text needs to appear adjacent to the date and additional text should appear within with the same cell with a line space. I have a list of events which run for almost four months. Is this possible using vba? Herewith I have pasted an example. Thanks a ton for your help.

[TABLE="width: 648"]
<colgroup><col width="72" span="9" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]04-Oct[/TD]
[TD]abcd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]05-Oct[/TD]
[TD]efgh[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]01-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]08-Oct[/TD]
[TD]ijkl[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]02-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]06-Oct[/TD]
[TD]mnop[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]03-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]04-Oct[/TD]
[TD]qrst[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]04-Oct[/TD]
[TD="class: xl64, width: 72"] abcd
qrst[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]05-Oct[/TD]
[TD] efgh[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]06-Oct[/TD]
[TD] mnop[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]07-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]08-Oct[/TD]
[TD] ijkl[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]09-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]10-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]11-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]12-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]13-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"]14-Oct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

Not sure if you've ever dabbled with Dictionaries, but sounds like a perfect fit. Here's a great tutorial on how they work: https://excelmacromastery.com/vba-dictionary/

 

[mikerickson]

You could use this UDF.

Assuming your short list is in columns A and B and your calendar of dates is in column D (intervening blank column)

You could put =ConcatIf(A:A, D1, B:B) in E1 and drag down.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

 

[bharath235]

hi mik,

this works .. but the only issue is that a line break should be available between the values, if there is more than one value for a cell .. something like alt+enter or chr(10). is that possible? thanks for your help

 

[mikerickson]

You can specify the delimiter to be used.

=ConcatIf(A:A, D1, B:B, CHAR(10))

 

[bharath235]

hi mik,

thanks for your response .. I tried this earlier, but did not work out .. the strings are all attached together ..

 

[bharath235]

Hi Mik and friends .. the following works

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
Dim i As Long, j As Long
With compareRange.Parent
Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With
If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
stringsRange.Column - compareRange.Column)

For i = 1 To compareRange.Rows.Count
For j = 1 To compareRange.Columns.Count
If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
End If
End If
Next j
Next i
ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

However, the strings need to be separated by a line space within the cell .. at the moment they are all together. I tried =ConcatIf(A:A, D1, B:B, CHAR(10)) as suggested by Mik, but it does not work. Could you please help in this regard?

Thanks!

 

[mikerickson]

Is the word-wrap set checked for the cell with the formula.
Have you tried CHAR(13)?

 

[bharath235]

Thanks, Mik
Works as expected. Just one doubt.. is it possible to handle a scenario wherein there is a date, but no event .. in such a case can the blank cell data be ignored?