Duplicates problem

  • Thread starter Thread starter hjopertham
  • Start date Start date
H

hjopertham

Hello,
can anyone help me find a solution to another slightly different
duplicates problem. I'm trying to count how many identical numbers
there are between two ranges that may contain duplicates.

Can anyone help me to edit a formula that Ron Rosenfeld so kindly
provided, so that it counts the matches?
=IF(SUMPRODUCT(--(COUNTIF(A2:H2,A1:H1)=COUNTIF(A1:H1,A1:H1)))=COUNT(A1:H1),"MATCH","No
Match")

Previous post's example
Range 1 A1:H1___ 8 2 8 14 3 6 4 8
Range 2 A2:H2___14 2 3 4 8 6 8 8
Formula result =8

example 2
Range 1 F1:J1____ 8 2 8 14 3 3 3 3 3
Range 2 O5:O_____8 14 14 3 3
Formula result =4

example 3
Range 1 F1:J1____ 8 2 8 14 3
Range 2 O5:O9____14 8 14 8 8
Formula result =3


The solution provided by Domenic to a different question does provide
the correct answers. Unfortunately his formula requires that I specify
the row and column so it isn't practical for my numerous needs.

All assistance gratefully received.

Regards
James
 
Hello,
can anyone help me find a solution to another slightly different
duplicates problem. I'm trying to count how many identical numbers
there are between two ranges that may contain duplicates.

Can anyone help me to edit a formula that Ron Rosenfeld so kindly
provided, so that it counts the matches?
=IF(SUMPRODUCT(--(COUNTIF(A2:H2,A1:H1)=COUNTIF(A1:H1,A1:H1)))=COUNT(A1:H1),"MATCH","No
Match")

Previous post's example
Range 1 A1:H1___ 8 2 8 14 3 6 4 8
Range 2 A2:H2___14 2 3 4 8 6 8 8
Formula result =8

example 2
Range 1 F1:J1____ 8 2 8 14 3 3 3 3 3
Range 2 O5:O_____8 14 14 3 3
Formula result =4

example 3
Range 1 F1:J1____ 8 2 8 14 3
Range 2 O5:O9____14 8 14 8 8
Formula result =3


The solution provided by Domenic to a different question does provide
the correct answers. Unfortunately his formula requires that I specify
the row and column so it isn't practical for my numerous needs.

All assistance gratefully received.

Regards
James
Click to expand...

If I understand you correctly, you want to look at each number in range 1. If
it appears in Range 2, count it as a match and then exclude that number in
range 2 from further consideration. So that, for example:

Rg1 8 8 8 8
Rg2 8

would count as 1, where as

Rg1 8 8 8 8
Rg2 8 8

would count as 2, as would

Rg1 8 8
Rg2 8 8 8 8

That being the case, it can be done with a UDF.

To enter this UDF, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter =NumMatches (rg1, rg2) into some cell where rg1 and rg2
refer to your ranges where you are looking for duplicates.

As written, the routine ignores blanks, but will compare text (case-sensitive)
as well as numbers.

===============================
Function NumMatches(rg1 As Range, rg2 As Range)
Dim i As Long, j As Long
Dim c As Range
Dim rg1nums()
Dim rg2nums()

ReDim rg1nums(1 To rg1.Count)
ReDim rg2nums(1 To rg2.Count)

i = 1
For Each c In rg1
rg1nums(i) = c.Value
i = i + 1
Next c

i = 1
For Each c In rg2
rg2nums(i) = c.Value
i = i + 1
Next c

NumMatches = 0
For i = 1 To UBound(rg1nums)
For j = 1 To UBound(rg2nums)
If rg1nums(i) = rg2nums(j) And _
Not IsEmpty(rg1nums(i)) And _
Not IsEmpty(rg2nums(j)) Then
NumMatches = NumMatches + 1
rg2nums(j) = ""
Exit For
End If
Next j
Next i

End Function
=========================

--ron
 
Thanks Ron Rosenfeld for the UDF.

Yes you understood my problem fully. I will extensively test it tonight
and get back to you. Once again thanks.

Regards
James.
 
Thanks Ron Rosenfeld for the UDF.

Yes you understood my problem fully. I will extensively test it tonight
and get back to you. Once again thanks.

Regards
James.
Click to expand...

Glad to help. Let me know how your testing works out.

Best wishes,

--ron
 
Back
Top