Can Excel do this?

[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

[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]...
>
> 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
>

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


In article
<[email protected]>,
streetfighter
<[email protected]> 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

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



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

[JE McGimpsey]

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

In article <[email protected]>,
"Biff" <[email protected]> wrote:

> =SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A2,ROW(INDIRECT("1:"&
> LEN(A2))),1)))

[streetfighter]

Thanks for your help, really helped me out.

[Biff]

Good point.

Biff

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> You might want to consider using LEN(A1) in both arguments - that way
> the arguments can be of different length.
>
> In article <[email protected]>,
> "Biff" <[email protected]> wrote:
>
>> =SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A2,ROW(INDIRECT("1:"&
>> LEN(A2))),1)))