计算两个列表之间的共享数 [英] 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屋！