Concatenate dynamic text values based on criteria in excel

Question

I have a worksheet were I am trying to concatenate dynamic text values based on =TODAY()

So I have B3:B1000 being the fields where users will enter in text. D3:D1000 is where the user enters the date they filled it in. I3 is =TODAY()

How do I concatenate text values in B3:B1000 based on if the dates in the D3:D1000 = I3? and have that concatenation always update based on I3?

I would also need a delimiter of ", "

Answer 1

Got it working after some trail and error and some deeper searching :)

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
    ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If strResult <> "" Then
    strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function

ErrHandler: ConcatenateIf = CVErr(xlErrValue) End Function

and then used this concatenate function =ConcatenateIf(D3:D1000,I3,B3:B1000,", ")