Posted by Aladin Akyurek on February 07, 2001 1:56 PM

It's an easy matter if you only want to compare values in A with values in B in the same row. Enter in C1 to compare te value of A1 with that of B1:

=IF(LEFT(A1,9)=LEFT(B1,9),"ok","")

It's a different matter if you want to compare the value of A1 pairwise with every value in column B.

Aladin

Posted by Tim on February 07, 2001 2:12 PM

Thanks very much. Unfortunately that's exactly what I'm trying to do. However, I think your function is almost exactly what I need.

Is there any way to keep the static range column from changing values? For example, the column C value in the function changes to C3:C13227, C4:C13228 when this function

is pasted down.

=IF(LEFT(A2,9)=LEFT(C2:C13226,9),"OK","")

Thanks very much for your help.

Tim

Posted by Mark W. on February 07, 2001 2:17 PM

=ISNUMBER(MATCH(LEFT(A1,9)&"*",List2!A:A,0))+0

where A1 is a value from the 1st column and List2!A:A is

a reference to your 2nd column. This formula will return

0 or 1. 1 means that there's a match; 0 means there isn't.

You can apply a number format to change these values to any

"flag" you wish. For example, [=1][green]"Y";[red]"N" will

produce a green "Y" or a red "N".

Posted by Tim on February 07, 2001 2:22 PM

=ISNUMBER(MATCH(LEFT(A1,9)&"*",List2!A:A,0))+0

Thanks a lot Mark. I will try this.

Tim

Posted by Aladin Akyurek on February 08, 2001 12:18 AM

I gather you have your data in A from A2 on that you want to compare with every value in C from C2 on. In order to do that: type in

D2 =IF(LEFT($A$2,9)=LEFT(C2:C13226,9),"OK","")

Copy down this formula as far as needed. To compare $A$3 with the values in the range C2:C13226, type the adjusted formula E2 and copy down, and so on. If A contains N values, you will need to use N columns to make the pairwise comparisons you want.

Aladin

Posted by Aladin Akyurek on February 08, 2001 12:28 AM

Make that formula:

D2 =IF(LEFT($A$2,9)=LEFT(C2),"OK","")

Aladin