CODEWITHSUNDEEP
excelexcel-formula
Max
Max

Reputation: 940

Counting the repeating values in comma-separated lists

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!

Upvotes: 0

Views: 228

Answers (2)

Nathan Sutherland
Nathan Sutherland

Reputation: 1260

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)

Upvotes: 1

JvdV
JvdV

Reputation: 75840

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...

enter image description here

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

Borrowed some knowledge from here

Upvotes: 2

Related Questions