CODEWITHSUNDEEP
excelvbaconcatenation
ommpi5991
ommpi5991

Reputation: 23

Excel CONCATENATE with multiple condition is it possible?

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?

Upvotes: 0

Views: 181

Answers (1)

Harun24hr
Harun24hr

Reputation: 36750

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)))

enter image description here

Upvotes: 1

Related Questions