How to compare two cells for the same numbers
 

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


--
sun1x
------------------------------------------------------------------------
sun1x's Profile: http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

sun1x Wrote:
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

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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

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(INDIREC T("2:"&C2+1))))-
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIREC T("1:"&C2))))-1),A2)))

--

HTH

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


"sun1x" wrote in
message ...

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


--
sun1x
------------------------------------------------------------------------
sun1x's Profile:
http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

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


--
sun1x
------------------------------------------------------------------------
sun1x's Profile: http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

Syntax is correct but the answer is 0. Could you please try these data
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!


--
sun1x
------------------------------------------------------------------------
sun1x's Profile: http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

Have you tried my formula?

--

HTH

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


"sun1x" wrote in
message ...

Syntax is correct but the answer is 0. Could you please try these data
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!


--
sun1x
------------------------------------------------------------------------
sun1x's Profile:
http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

Bob Phillips Wrote:
Have you tried my formula?



Yes, Bob.

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

I also tried to put the formulas at the bottom of the records to allow
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 sheet
and I really hate to do it manually.

Thanks a lot!


--
sun1x
------------------------------------------------------------------------
sun1x's Profile: http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

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(INDIREC T("1:"&C2)))),
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIREC T("2:"&C2+1))))-
FIND("~",SUBSTITUTE(","&B2&",",",","~",ROW(INDIREC T("1:"&C2))))-1),A2)))

and copy down

--

HTH

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


"sun1x" wrote in
message ...

Bob Phillips Wrote:
Have you tried my formula?



Yes, Bob.

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

I also tried to put the formulas at the bottom of the records to allow
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 sheet
and I really hate to do it manually.

Thanks a lot!


--
sun1x
------------------------------------------------------------------------
sun1x's Profile:
http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

sun1x Wrote:
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 " ".

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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

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!


--
sun1x
------------------------------------------------------------------------
sun1x's Profile: http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

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)


"sun1x" wrote in
message ...

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!


--
sun1x
------------------------------------------------------------------------
sun1x's Profile:
http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

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

Thanks


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

--
sun1x
------------------------------------------------------------------------
sun1x's Profile: http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

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)


"sun1x" wrote in
message ...

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

Thanks


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

--
sun1x
------------------------------------------------------------------------
sun1x's Profile:
http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096

How to compare two cells for the same numbers
 

Perfect! How do you manage to write such a formula! Are you the dad of
MS-Excel?


--
sun1x
------------------------------------------------------------------------
sun1x's Profile: http://www.excelforum.com/member.php...o&userid=28327
View this thread: http://www.excelforum.com/showthread...hreadid=479096