VBA Excel-如何在Excel数组中显示非相等值 [英] VBA Excel - How to display non equal values in an excel array

问题描述

所以目前我在Excel中有两个DATA输入数据1和数据2我需要一个公式或某种形式来显示结果*目前，这是一个手动过程.

So currently I have two DATA inputs in excel Data 1 and Data 2 I need a formula or some sort to display results *currently this is a manual process.

推荐答案

使用 Filter()函数

Approach using Filter() function

Function test(ByVal a, ByVal b, Optional Delim$ = ",") As String
'Purpose: return non unique values by comparing two comma separated string lists a vs. b
a = Split(a, Delim): b = Split(b, Delim)
Dim elem
For Each elem In b
    a = Filter(a, elem, False)  ' Include:=False, i.e. exclude non uniques
Next elem
test = Join(a, Delim & " ")
End Function

参数a::668,669,777,778,779,780,781,782,891,893,894,895

Argument a: 668,669,777,778,779,780,781,782,891,893,894,895

参数b: 668,777,779,778,780,892,891,782

Argument b: 668,777,779,778,780,892,891,782

结果字符串 669、781、893、894、895

Result string 669, 781, 893, 894, 895

BTW 结果不应该包含782(c.f. OP)，因为这不是唯一项.

BTW the result shouldn't include 782 (c.f. OP) as this isn't a unique item.

提示/编辑

以上方法仅假设三个数字.基本上 Filter 函数执行部分字符串搜索，如果您要搜索例如因此， 7 也排除了任何包含 7 的数字.~~>查看修改后的代码

The above approach assumes three figure numbers only. As basically the Filter function executes a partial string search this would lead to unwanted results if you would be searching for e.g. 7 thus excluding any number containing 7, as well. ~~> See revised code

修改后的代码

仅允许完整的字符串匹配:

Allows only full string matches:

变体a)第二个列表包含双连字符，但仅是第一个列表的子集

Function test(ByVal a, ByVal b, Optional Delim$ = ",") As String
'Purpose: return non unique values by comparing two comma separated string lists a vs. b
a = "$" & Replace(a, Delim, "$" & Delim & "$") & "$"
a = Split(a, Delim): b = Split(b, Delim)
Dim elem
For Each elem In b
    a = Filter(a, "$" & elem & "$", False)  ' Include:=False, i.e. exclude non uniques
Next elem
test = Replace(Join(a, Delim & " "), "$", vbNullString)
End Function

变体b)第二个列表还包含与第一个列表不同的新数字

这可能是您的问题的防水解决方案(结果与@RonRosenfeld的好的解决方案相对应).

This might be the waterproof solution to your question (results corresponding to @RonRosenfeld 's fine solution).

Function test(ByVal a, ByVal b, Optional Delim$ = ",", Optional ByVal cnt% = 1) As String
'Purpose: return non unique values by comparing two comma separated string lists a vs. b
If cnt = 1 Then               ' recursive call to check 2nd string against 1st one
    test = test(b, a, Delim, 2)
    If Len(test) > 1 Then test = Mid(test, 1, Len(test) - 1)
End If
a = "$" & Replace(a, Delim, "$" & Delim & "$") & "$"
a = Split(a, Delim): b = Split(b, Delim)
Dim elem
For Each elem In b
    a = Filter(a, "$" & elem & "$", False)  ' Include:=False, i.e. exclude non uniques
Next elem
test = Replace(Join(a, Delim), "$", vbNullString) & Delim & test
If cnt = 0 Then test = Replace(test, Delim, Delim & " ")  ' add blank after delimiters
End Function

这篇关于VBA Excel-如何在Excel数组中显示非相等值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！