columns match

[jasman]

Hi I have two columns A and B. I need excel to return "true" in another column if the values in both match. However column A has some letters before the number I need to match. Is there any way of doing this: For examples
Column A =
<TABLE style="WIDTH: 103pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=137 border=0 x:str><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 103pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: red" width=137 height=17>4R8Q-19A216-AC</TD></TR></TBODY></TABLE>

Column B= 19A216

I need thsi to return TRUE even though its a partial match#

thanks

 

[MrKowz]

Try using wildcards:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">29Y421</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">9N6J-12P412-AT</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">4R8Q-19A216-AC</td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=MATCH(<font color="Blue">"*19A216*",A1:A3,0</font>)</td></tr></tbody></table></td></tr></table><br />

 

[edokhotnik]

Hi I have two columns A and B. I need excel to return "true" in another column if the values in both match. However column A has some letters before the number I need to match. Is there any way of doing this: For examples
Column A =
<TABLE style="WIDTH: 103pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=137 x:str><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; WIDTH: 103pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 height=17 width=137>4R8Q-19A216-AC</TD></TR></TBODY></TABLE>

Column B= 19A216

I need thsi to return TRUE even though its a partial match#

thanks

Are the "4R8Q-" and the "-AC" on all or only on some?

 

[jasman]

thanks. I have a few hundred rows though. Is there anyway to make this formual generic instead of having to add part of the code to it?

Yes the a8rc and AC are on all rows but the letters and numbers differ

 

[MrKowz]

Something like this?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">29Y421</td><td style=";">19A216</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">9N6J-12P412-AT</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">4R8Q-19A216-AC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=ISNUMBER(<font color="Blue">MATCH(<font color="Red">"*"&B1&"*",A:A,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[jasman]

thats what im looking for, but I realised that some of the cells have for example <TABLE style="WIDTH: 103pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=137 border=0 x:str><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 103pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffff" width=137 height=17>1L2T-14K147-AA</TD></TR></TBODY></TABLE>
but im looking to see if 1rt-14-AA is True?

 

[MrKowz]

What would be your desired return for that, and what would its match be if you want it to return TRUE?

 

[jasman]

Currently with that formula it returns TRUE, but it should be false as there is no cell containing the value ***-14-***

 

[MrKowz]

I'm not sure if I quite follow... what exactly are your inputs? When I use the formula, it works fine for me.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;">1L2T-14K147-AA</td><td style="border-left: 1px solid black;;">-14-</td><td style="text-align: right;;">FALSE</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=ISNUMBER(<font color="Blue">MATCH(<font color="Red">"*"&B1&"*",A:A,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[jasman]

<TABLE style="WIDTH: 391pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=521 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 39pt" height=52><TD class=xl24 style="BORDER-RIGHT: purple 1pt solid; BORDER-TOP: purple 1pt solid; BORDER-LEFT: white 1.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: purple 1pt solid; HEIGHT: 39pt; BACKGROUND-COLOR: #ccffcc" width=64 height=52 x:num>101</TD><TD class=xl25 style="BORDER-RIGHT: purple 1pt solid; BORDER-TOP: purple 1pt solid; BORDER-LEFT: purple; WIDTH: 48pt; BORDER-BOTTOM: purple 1pt solid; BACKGROUND-COLOR: #fff28e" width=64 x:num>14</TD><TD class=xl26 style="BORDER-RIGHT: purple 1pt solid; BORDER-TOP: purple 1pt solid; BORDER-LEFT: purple; WIDTH: 48pt; BORDER-BOTTOM: purple 1pt solid; BACKGROUND-COLOR: #ccffcc" width=64>DECAL PASS WRNG</TD><TD class=xl26 style="BORDER-RIGHT: purple 1pt solid; BORDER-TOP: purple 1pt solid; BORDER-LEFT: purple; WIDTH: 48pt; BORDER-BOTTOM: purple 1pt solid; BACKGROUND-COLOR: #ccffcc" width=64>BES_301 - Exterior Trim</TD><TD class=xl26 style="BORDER-RIGHT: purple 1pt solid; BORDER-TOP: purple 1pt solid; BORDER-LEFT: purple; WIDTH: 48pt; BORDER-BOTTOM: purple 1pt solid; BACKGROUND-COLOR: #ccffcc" width=64>Airbag Warning Label</TD><TD class=xl27 style="BORDER-RIGHT: white 1.5pt solid; BORDER-TOP: purple 1pt solid; BORDER-LEFT: purple; WIDTH: 48pt; BORDER-BOTTOM: purple 1pt solid; BACKGROUND-COLOR: #ccffcc" width=64>FZDO7</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 55pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=73 x:bool="TRUE">TRUE</TD></TR></TBODY></TABLE>

It shows at TRUE for me as above . It should be false.

The formula is:

=ISNUMBER(MATCH("*"&B2&"*",supplierPartListSearch!$B$2:$B$19896,0))