不匹配行单元格的公式和一列中的显示值 [英] Formula for unmatched row cell and display value in one column

问题描述

不相匹配的行单元格的公式是什么，并在附件工作表中提到的
在一列中显示。

获取Result和Header列值的工作表

我使用这个公式：

  = IFERROR（VLOOKUP（A2，A2：M2,1， FALSE），）
  

但它在Result列中只显示一个值。

解决方案

本机工作表公式根本不能处理字符串连接。即使是正在引入的新字符串函数，例如

---

¹ _{TEXTJOIN功能正在与Excel 2016 / Office 365 / Excel Online一起推出。}

²_{新的 CONCAT功能用于替换旧的 CONCATENATE函数。 sub>}

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.

解决方案

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

这篇关于不匹配行单元格的公式和一列中的显示值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！