Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
How do I hide the zero in the "sum" cell until numbers are put in the cells that are being added? | Excel Worksheet Functions | |||
substracting numbers in non contigous cells | Excel Discussion (Misc queries) | |||
How to compare 3 numbers and return value | Excel Worksheet Functions | |||
update row numbers after different active cells in macros followi. | Excel Discussion (Misc queries) |