Compare two columns using wildcards
Posted by Tim on February 07, 2001 1:38 PM
I'm trying to compare the data in one range with the data in another range. If the first nine digits of data
in column one match the first nine digits of data in column two, then I would like to place a flag in a column beside the first column. Any ideas? Thanks very much.
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
