Create a comma separated string in cell based on unique values in 2 columns-CodePudding

I'm not sure if this is possible, but I would like to combine data into a single row using either an Excel formula or VBA code. I'm using Excel 2010.

In the image below the unique data that joins the Result Output in ColD is separated by the unique Event ID in ColA and the ID in ColB, so if the EventID and ID (ColA and B) are the same - the data in column C, needs to be joined as a comma separated string in ColD.

So the data in column D is what I would like to achieve and do not know how.

CodePudding user response：

Put the following code in a regular module:

Function TEXTJOINIFS(rng As Range, delim As String, ParamArray arr() As Variant)
    Dim rngarr As Variant
    rngarr = Intersect(rng, rng.Parent.UsedRange).Value

    Dim condArr() As Boolean
    ReDim condArr(1 To Intersect(rng, rng.Parent.UsedRange).Rows.Count) As Boolean


    Dim i As Long
    For i = LBound(arr) To UBound(arr) Step 2
        Dim colArr() As Variant
        colArr = Intersect(arr(i), arr(i).Parent.UsedRange).Value
        Dim j As Long
        For j = LBound(colArr, 1) To UBound(colArr, 1)

            If Not condArr(j) Then
                Dim charind As Long
                charind = Application.Max(InStr(arr(i   1), ">"), InStr(arr(i   1), "<"), InStr(arr(i   1), "="))
                Dim opprnd As String
                If charind = 0 Then
                    opprnd = "="
                Else
                    opprnd = Left(arr(i   1), charind)
                End If
                Dim t As String
                t = """" & colArr(j, 1) & """" & opprnd & """" & Mid(arr(i   1), charind   1) & """"
                If Not Application.Evaluate(t) Then condArr(j) = True
            End If
        Next j
    Next i

    For i = LBound(rngarr, 1) To UBound(rngarr, 1)
        If Not condArr(i) Then
            TEXTJOINIFS = TEXTJOINIFS & rngarr(i, 1) & delim
        End If
    Next i

    TEXTJOINIFS = Left(TEXTJOINIFS, Len(TEXTJOINIFS) - Len(delim))

End Function

Then in D2 put:

=IF(OR(A2<>A3,B2<>B3),TEXTJOINIFS(C:C,", ",A:A,A2,B:B,B2),"")

CodePudding user response：

You do not need to rely on VBA if your version of Excel supports TEXTJOIN.

In this case you can use the following formula in D2 then copy down to join the matches:

TEXTJOIN(",",TRUE,IF(A2&"#"&B2=A$2:A$13&"#"&B$2:B$13,C$2:C$13,""))

If you then wrap it in an IF statement to count current match occurrences versus overall matches you can add in the filtering to give:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=COUNTIFS(A:A,A2,B:B,B2),TEXTJOIN(",",TRUE,IF(A2&"#"&B2=A$2:A$13&"#"&B$2:B$13,C$2:C$13,"")),"")

These formulae also use a delimiter to avoid spill between column values.

e.g. AB & CD = A & BCD, but AB & # & CD /= A & # & BCD