Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can Excel do this?
didnt know where to put this so i thought here would be best, basicaaly i want to be able to compare 2 cells. For example say i have one cell with 1111000111, and another with 1100111000 is it possible to get a result of 2. Because 1111000111 1100111000 the first two letters are the same so there are 2 "correct" restults, is there any code which i can put in for complicated versions of this, thanks -- streetfighter ------------------------------------------------------------------------ streetfighter's Profile: http://www.excelforum.com/member.php...o&userid=26324 View this thread: http://www.excelforum.com/showthread...hreadid=395960 |
#2
|
|||
|
|||
Hi!
So, if the numbers we A1 = 1111000111 A2 = 1100111001 Would the correct answer then be 3? If that's the case: =SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A2,ROW( INDIRECT("1:"&LEN(A2))),1))) Biff "streetfighter" wrote in message news:streetfighter.1ttvud_1124147111.8985@excelfor um-nospam.com... didnt know where to put this so i thought here would be best, basicaaly i want to be able to compare 2 cells. For example say i have one cell with 1111000111, and another with 1100111000 is it possible to get a result of 2. Because 1111000111 1100111000 the first two letters are the same so there are 2 "correct" restults, is there any code which i can put in for complicated versions of this, thanks -- streetfighter ------------------------------------------------------------------------ streetfighter's Profile: http://www.excelforum.com/member.php...o&userid=26324 View this thread: http://www.excelforum.com/showthread...hreadid=395960 |
#3
|
|||
|
|||
One way (array-entered: CTRL-SHIFT-ENTER of CMD-RETURN):
=SUM(--(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1) = MID(A2,ROW(INDIRECT("1:" & LEN(A1))),1))) In article , streetfighter wrote: didnt know where to put this so i thought here would be best, basicaaly i want to be able to compare 2 cells. For example say i have one cell with 1111000111, and another with 1100111000 is it possible to get a result of 2. Because 1111000111 1100111000 the first two letters are the same so there are 2 "correct" restults, is there any code which i can put in for complicated versions of this, thanks |
#4
|
|||
|
|||
I'm not sure what complicated means, but this worked for me:
=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) =MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))) (all one cell) And with some minor error checking: =IF(LEN(A1)<LEN(A2),"Not same length!", SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) =MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))) (still all one cell) streetfighter wrote: didnt know where to put this so i thought here would be best, basicaaly i want to be able to compare 2 cells. For example say i have one cell with 1111000111, and another with 1100111000 is it possible to get a result of 2. Because 1111000111 1100111000 the first two letters are the same so there are 2 "correct" restults, is there any code which i can put in for complicated versions of this, thanks -- streetfighter ------------------------------------------------------------------------ streetfighter's Profile: http://www.excelforum.com/member.php...o&userid=26324 View this thread: http://www.excelforum.com/showthread...hreadid=395960 -- Dave Peterson |
#5
|
|||
|
|||
You might want to consider using LEN(A1) in both arguments - that way
the arguments can be of different length. In article , "Biff" wrote: =SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A2,ROW( INDIRECT("1:"& LEN(A2))),1))) |
#6
|
|||
|
|||
Good point.
Biff "JE McGimpsey" wrote in message ... You might want to consider using LEN(A1) in both arguments - that way the arguments can be of different length. In article , "Biff" wrote: =SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A2,ROW( INDIRECT("1:"& LEN(A2))),1))) |
#7
|
|||
|
|||
Thanks for your help, really helped me out. -- streetfighter ------------------------------------------------------------------------ streetfighter's Profile: http://www.excelforum.com/member.php...o&userid=26324 View this thread: http://www.excelforum.com/showthread...hreadid=395960 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |