计算两个列表之间的共享数 [英] Counting the shared numbers between two lists
本文介绍了计算两个列表之间的共享数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在两个单元格中有两个列表.列表中包含带逗号的数字.
I have two lists in two cells. The lists contain numbers with commas between them.
例如
A1: 1,2,3,4,10,12
A2: 1,2,3,5
我需要计算两个单元之间的共享数.
结果应为B1 : 3
(因为1,2,3仅共享唯一数字)
I need the count of the shared numbers between two cells.
The result would be in B1 : 3
(because 1,2,3 are only shared unique numbers)
另一个例子:
A3: 10,12,14,14
A4: 14,14,16
在这种情况下,结果为1.(因为14,只有一个共享的唯一数字)
in this case the result is 1. (because 14, there is only one shared unique number)
有人可以给我建议一个公式吗?请.宏解决方案也可以. 谢谢.
Can someone advise me a formula for this? Please. a macro solution would be fine too. Thank you.
* 只是一个解决方案,请不要将数字分成不同的列.
推荐答案
请尝试下一个功能:
Function ListCompare(L1 As String, L2 As String) As Long
Dim arr1 As Variant, arr2 As Variant, arrFin As Variant, k As Long
Dim El1 As Variant, El2 As Variant, ElF As Variant, boolFound
If L1 = "" Or L2 = "" Then ListCompare = 0: Exit Function
arr1 = Split(L1, ","): arr2 = Split(L2, ",")
ReDim arrFin(UBound(arr1) + UBound(arr2))
For Each El1 In arr1
For Each El2 In arr2
If El1 = El2 Then
For Each ElF In arrFin
If ElF = El1 Then boolFound = True: Exit For
Next
If Not boolFound Then arrFin(k) = El1: k = k + 1
boolFound = False
End If
Next
Next
If k > 0 Then
ReDim Preserve arrFin(k - 1)
ListCompare = UBound(arrFin) + 1
Else
ListCompare = 0
End If
End Function
可以通过以下方式调用/测试:
It can be call/tested in the next way:
Sub testListCompare()
Dim L1 As String, L2 As String
L1 = "1,2,3,4,10,12": L2 = "1,2,3,5"
L1 = "10,12,14,14": L2 = "14,14,16"
Debug.Print ListCompare(L1, L2)
End Sub
这篇关于计算两个列表之间的共享数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文