Counting the shared numbers between two lists

Question

I have two lists in two cells. The lists contain numbers with commas between them.

e.g.

A1:  1,2,3,4,10,12
A2:  1,2,3,5

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)

another example:

A3: 10,12,14,14
A4: 14,14,16

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.

*only a solution without separating the numbers into different columns please.

I think not, when i type unique in, nothing comes up in the cell. — Max, Jul 20 '20 at 18:50

JvdV · Answer 1 · 2020-07-22T07:16:18.063

No need for VBA per se. In Excel 2016 you can use:

=COUNT(FILTERXML("<t>,"&A1&",<s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s[contains(../text(), concat(',',. ,','))][not(preceding::*=.)]"))

Based on this post. See the results for your samples:

The XPATH will return an array of <s> nodes where it's parent-node's text contains a concatenation between comma's and the <s> node itself. Furthermore we make sure to check for duplicates in preceding siblings.

FaneDuru · Accepted Answer · 2020-07-20T19:07:43.867

3

Try the next function, please:

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

edited Jul 20 '20 at 19:07

answered Jul 20 '20 at 18:51

FaneDuru

38,298
4
19
27

Thanks, FaneDuru. I tested it with the formula, and it works perfectly. – Max Jul 20 '20 at 18:54
1

@Max: Glad I could help! – FaneDuru Jul 20 '20 at 18:59
1

@Max: It is only an example. It must be improved, from error handling point of view... I will adapt it to also accept empty strings and return 0 (not an error)... – FaneDuru Jul 20 '20 at 19:05
Luckily i have no empty strings :) – Max Jul 20 '20 at 19:09
1

@Max: Normally, you do not, but I could see many wanders in some Excel workbooks. Made by mistakes, of course... The code must be prepared to also deal with such a situation... :) – FaneDuru Jul 20 '20 at 19:10

Counting the shared numbers between two lists

2 Answers2

Linked