[Solved] Formula for unmatched row cell and display value in one column

Question

Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function¹ and the updated CONCAT function² have difficulty with conditional concatenation beyond TEXTJOIN’s blank/no-blank parameter.

Here are a pair of User Defined Functions (aka UDF) that will perform the tasks.

Function udfUniqueList(rng As Range, _
        Optional delim As String = ",")
    Dim str As String, r As Range

    'always truncate ranges as parameters to the usedrange
    Set rng = Intersect(rng, rng.Parent.UsedRange)

    str = rng(1).Value2
    For Each r In rng
        If Not CBool(InStr(1, delim & str & delim, delim & r.Value2 & delim, vbTextCompare)) Then
            str = str & delim & r.Value2
        End If
    Next r

    udfUniqueList = str

End Function

Function udfRogueHeaders(rng As Range, hdr As Range, _
        Optional delim As String = ",", _
        Optional bBlnks As Boolean = False)
    Dim i As Long, bas As String, str As String

    'always truncate ranges as parameters to the usedrange
    Set rng = Intersect(rng, rng.Parent.UsedRange)

    'reshape hdr to be identical to rng
    Set hdr = hdr.Resize(rng.Rows.Count, rng.Columns.Count)

    bas = rng(1).Value2
    For i = 1 To rng.Cells.Count
        If (CBool(Len(UCase(rng.Cells(i).Value2))) And Not bBlnks) Or _
          bBlnks Then
            If UCase(bas) <> UCase(rng.Cells(i).Value2) Then
                str = str & IIf(CBool(Len(str)), delim, vbNullString) & _
                        hdr.Cells(i).Value2
            End If
        End If
    Next i

    udfRogueHeaders = str

End Function

In P2:Q2 as,

=udfUniqueList(A2:L2)
=udfRogueHeaders(A2:L2, A$1:L$1)

Results:

¹ _{The TEXTJOIN function is being introduced with Excel 2016 ⁄ Office 365 ⁄ Excel Online. It is not available in earlier versions.}

² _{The new CONCAT function for Excel 2016 ⁄ Office 365 ⁄ Excel Online is intended to replace the older CONCATENATE function with improved functionality.}

Accepted Answer