Duplicates problem

[hjopertham]

Hello
Can anyone help me find a solution to the following question:
Comparing or counting two ranges that contain duplicates

Worksheet set-up
Range 1 A1:H1___ 8 2 8 14 3 6 4 8
Range 2 A2:H2___14 2 3 4 8 6 8 8

Basically I'm simply trying to compare two ranges to see if they
match and have identical numbers and in exactly the same quantity. My
problem is that the ranges contain duplicates. I've tried a variety
of COUNTIF, IF, or SUMPRODUCT formulas but these produce the wrong
result.

The formula result for the above should be =MATCH or =8

So what formula could I use to solve the above problem? All assistance
gratefully received

Regards
James.

 

[Domenic]

Assuming that A1:H2 contains...

8 2 8 8 3 6 4 8
14 2 3 4 8 6 8 8

....and that the answer should be 7, try the following formula...

=SUMPRODUCT((COUNTIF(A2:H2,A1:H1)>0)*(COUNTIF(OFFSET(A1:H1,0,0,1,COLUMN(A
1:H1)-ROW(A1)+1),A1:H1)<=COUNTIF(A2:H2,A1:H1)))

Hope this helps!

 

[Ron Rosenfeld]

Hello
Can anyone help me find a solution to the following question:
Comparing or counting two ranges that contain duplicates

Worksheet set-up
Range 1 A1:H1___ 8 2 8 14 3 6 4 8
Range 2 A2:H2___14 2 3 4 8 6 8 8

Basically I'm simply trying to compare two ranges to see if they
match and have identical numbers and in exactly the same quantity. My
problem is that the ranges contain duplicates. I've tried a variety
of COUNTIF, IF, or SUMPRODUCT formulas but these produce the wrong
result.

The formula result for the above should be =MATCH or =8

So what formula could I use to solve the above problem? All assistance
gratefully received

Regards
James.

Perhaps:

=IF(SUMPRODUCT(--(COUNTIF(A2:H2,A1:H1)=
COUNTIF(A1:H1,A1:H1)))=COUNT(A1:H1),"MATCH","No Match")

(Not extensively tested).



--ron

 

[hjopertham]

Hello,
can anyone help me find a solution to a slightly different duplicates
problem: Counting values common to two different sized ranges that may
contain duplicates.

Worksheet setup:
Range 1.......A1:H1___ 1 2 3 4 5 6 7 8

Range 2.......K1:T1___ 30 8 32 6 34 35 8 37 38 8

How do I count how many numbers in Range1 are found in Range2 (ignoring
the duplicates). The above result=2

Range1 is 8 cells, and does not contain duplicates
Range 2 is 10 cells and may or may not contain duplicates.

As a follow-up question how would I extract the unique values common to
both ranges, bearing in mind that the two ranges are different sizes?
Result= 6, 8

I've tried a variety formulas including adapting Pearson's duplicates
formulas, but I can't seem to always get the correct results when
extensively tested. All assistance gratefully received.
Regards
James.

 

[Dave Peterson]

This worked for me (to return the 2):
=SUMPRODUCT(--(COUNTIF(K1:T1,A1:H1)>0))

And I think I'd put a formula like:
=COUNTIF($K$1:$T$1,A1)
in A2 and drag across.

Then you could pick out the values larger than 0.

If your data were arranged vertically, you could use data|filter|autofilter to
show those values greater than 0 and copy|pasete them to a different range.