Can Excel do this?

S

streetfighter

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
 
B

Biff

Hi!

So, if the numbers were:

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" <[email protected]>
wrote in message
news:[email protected]...
 
J

JE McGimpsey

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)))


streetfighter
 
D

Dave Peterson

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)
 
J

JE McGimpsey

You might want to consider using LEN(A1) in both arguments - that way
the arguments can be of different length.
 
Top