Counting the repeating values in comma-separated lists

Question

My data in column A has comma-separated values (one/two digit numbers). I need a way to count the number of repeats in two consecutive rows.

Example data:

DATA              Expected Results
1,2,3,4,5                4
2,3,4,5                  2
3,4                      0
10,11,40,60,72,75        3
10,40,3,75

Note that each list doesn't have the same number of values. A1 has 5 values, A2 has 4, A3 has 2, etc.

I'd appreciate any help. Thanks!

I prefer a formula if possible. but I'm open to vba too. I know this is a little complicated. thanks. — Max, Jun 16 '19 at 12:27

score 2 · Accepted Answer · answered Jun 16 '19 at 13:20

Here is one option for B2:

=IFERROR(SUMPRODUCT(--(1*TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99))=(TRANSPOSE(1*TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1))-1)*99+1,99)))))),"")

Enter through CtrlShiftEnter

Drag down...

Picture shows dots as I couldn't use commas due to my version of Excel.

Borrowed some knowledge from here

score 1 · Answer 2 · answered Jun 16 '19 at 13:46

as a VBA alternative

Public Function commonCount(aRng As Range, bRng As Range) As Integer
Dim bArr() As String, aArr() As String, a As Integer, b As Integer

aArr = Split(aRng.Value, ",")
bArr = Split(bRng.Value, ",")

For a = LBound(aArr) To UBound(aArr)
    For b = LBound(bArr) To UBound(bArr)
        If aArr(a) = bArr(b) Then
            commonCount = commonCount + 1
            GoTo nexta
        End If
    Next b
nexta:
Next a

End Function

In B2 commonCount(A2,A3)

Counting the repeating values in comma-separated lists

2 Answers2