Formula for unmatched row cell and display value i

Question

What is the formula for unmatched row cell and display in one column as mentioned in the attached work sheet

Worksheet to get Result and Header column values

I am using this formula:

=IFERROR(VLOOKUP(A2,A2:M2,1,FALSE),"") 

But it displays only one values in Result column.

Answer 1

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.}