How to compare two cells for the same numbers

S

sun1x

Is there a way to compare two cells to see how many
numbers are same?

For example, A2 contains 16, 17, 19; B2 contains 16, 17; and I want to
put
in C2 the result 2. What is the formula for this?

Thanks
 
B

BenjieLop

sun1x said:
Is there a way to compare two cells to see how many
numbers are same?

For example, A2 contains 16, 17, 19; B2 contains 16, 17; and I want to
put
in C2 the result 2. What is the formula for this?

Thanks
Click to expand...

I will do this in two steps.

First, determine what numbers in Column B are in Column A. The common
numbers will be identified as "Duplicate" and the formula is

=IF(COUNTIF($A$2:$A$50,B2)>0,\"DUPLICATE\",\"\")

This formula can be entered in, say, Cell D2 and copied down until the
range requirement is met. Column D will then be the "helper" column and
this can be hidden.

In Cell C2, enter this formula

=COUNTIF(D2:D50,\"DUPLICATE\")

There may be a more elegant and efficient solution to your problem
but, meantime, this will work for you.

Regards.
 
B

Bob Phillips

Put this formula in C2

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

and this in D2

=SUMPRODUCT(--ISNUMBER(FIND(MID(B2,FIND("~",SUBSTITUTE(","&B2&",",",","~",RO
W(INDIRECT("1:"&C2)))),
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIRECT("2:"&C2+1))))-
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIRECT("1:"&C2))))-1),A2)))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

sun1x

Thanks Benjielop

But it does not work.

The first formula will put in D2 either a "Duplicate" or a " " when a
exact "string" match is found. If A2 has "15, 16, 17", and B2 has "15
16, 19", the result is " "
 
S

sun1x

Syntax is correct but the answer is 0. Could you please try these dat
out in a spreadsheet:

(A1: ) Indexed (B1: ) Relevant (C1: ) Correct
(A2: ) 84, 160, 161, 162, 163, 176 (B2: ) 160, 161, 162, 163
(A3: ) 28, 29, 30, 117, 118 (B3: ) 29, 116, 117, 126
(A4: ) 95, 96, 97, 109, 110, 111 (B4: ) 95, 96, 97, 109
(A5: ) 38, 39, 41, 42, 62, 135, 136, 138 (B5: ) 41, 42, 57, 123, 136
138
(A6: ) 67, 160, 164, 169, 170, 171, 172, 175, 176, 179 (B6: ) 67, 90

Thanks
 
B

Bob Phillips

Have you tried my formula?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

sun1x

Bob said:
Have you tried my formula?
Click to expand...

Yes, Bob.

The answer is always 0 when I put your formulas in C2 and D2, and dra
down to fill up the whole sheet. I guess because there are data i
A3,4,5... and B3,4,5..., and the number of items in each cell is no
even.

I also tried to put the formulas at the bottom of the records to allo
more room for the indirect commend, this time some answers are correct
but most of the answers are still 0. I have 120 records in this shee
and I really hate to do it manually.

Thanks a lot
 
B

Bob Phillips

The fact there is data in A3,4,5, and they are not always even is irrelevant
to my formula. It works on all of the examples you have posted to date
(assuming I understand your requirement correctly), never returning 0.

It might just be wrap-around in the NG posting, so try this amended version
of the second formula

=SUMPRODUCT(--ISNUMBER(FIND(MID(B2,
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIRECT("1:"&C2)))),
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIRECT("2:"&C2+1))))-
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIRECT("1:"&C2))))-1),A2)))

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

BenjieLop

sun1x said:
Thanks Benjielop

But it does not work.

The first formula will put in D2 either a "Duplicate" or a " " when an
exact "string" match is found. If A2 has "15, 16, 17", and B2 has "15,
16, 19", the result is " ".
Click to expand...

I am sorry I misunderstood your post. I should have read it more
carefully and realize that there are numerous numbers in a cell. I
guess I just skimmed through it and "assumed" that there was only one
number entered per cell.

My apologies ...

Regards.
 
S

sun1x

You are very clever! But there are still lots of errors. It might be
caused by space before or after a number.

Thanks a lot!
 
B

Bob Phillips

Don't think so mate, I tried it with spaces as well.

Post me a workbook and let me take a look.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

sun1x

I do not know how to post the file to you, I attach the file here, onc
you download it, change the file name extension to .xls

Thank

+-------------------------------------------------------------------
|Filename: Sample.txt
|Download: http://www.excelforum.com/attachment.php?postid=3952
+-------------------------------------------------------------------
 
B

Bob Phillips

You are absolutely right, it was the spaces. This one should work for you

=SUMPRODUCT(--ISNUMBER(FIND(MID(SUBSTITUTE(B2," ",""),
FIND("~",SUBSTITUTE(","&SUBSTITUTE(B2,"
","")&",",",","~",ROW(INDIRECT("1:"&C2)))),
FIND("~",SUBSTITUTE(","&SUBSTITUTE(B2,"
","")&",",",","~",ROW(INDIRECT("2:"&C2+1))))-
FIND("~",SUBSTITUTE(","&SUBSTITUTE(B2,"
","")&",",",","~",ROW(INDIRECT("1:"&C2))))-1),A2)))

I tested with your data and go tthe results you predicted (bar 2, which are
mistakes on your part I think :))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top