Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
434 views
in Technique[技术] by (71.8m points)

excel - Formula for unmatched row cell and display value in one column

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

[1]: http://i.stack.imgur.com/6A8dh.png

I am using this formula:

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

But it displays only one values in Result column.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function1 and the updated CONCAT function2 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:

udfRogueHeaders


1 The TEXTJOIN function is being introduced with Excel 2016 ? Office 365 ? Excel Online. It is not available in earlier versions.

2 The new CONCAT function for Excel 2016 ? Office 365 ? Excel Online is intended to replace the older CONCATENATE function with improved functionality.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...