Excel CONCATENATE with multiple condition is it possible?

Question

How to use CONCATENATE with multiple condition?

it is possible?

For example

i have

A	B	C	D
ID	Name	Date	Status
202	JJ	3/4/22	Absent
202	JJ	3/3/22	Late
201	JC	3/1/22	Early Out
201	JC	3/2/22	Late

i want to join it by C:C>E1 and C:C <E2 Where B:B =E3

E	F
3/1/22	-----
3/4/22	----
JJ	3/4/22 Absent, 3/3/22 Late
JC	3/1/22 Early Out, 3/2/22 Late

is this possible?

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim xResult As String
    On Error Resume Next
    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
    If ConcatenateRange.Cells(i).Value <> "" Then
    xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
    End If
    Next i
    If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
    End If
    ConcatenateIf = xResult
    Exit Function
    End Function

i already try this but it only join using single condition

is there any posible solution like formula or vba?

Answer 1

If you have Microsoft-365 then can use below formula in F4 cell then drag down.

=TEXTJOIN(", ",TRUE,FILTER(TEXT($C$2:$C$5,"M/d/yy") & " " &$D$2:$D$5,($C$2:$C$5>=$E$2)*($C$2:$C$5<=$E$3)*($B$2:$B$5=E4)))