Compare, truncate, compare again, write (VBA)

[rinser]

Hi guys,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have two ranges of numbers stored as text on two different sheets.
The first one is from product code and the second is from product category.
The problem is both ranges are of different lengths and I have to find out if a product from the right is part of a particular product category. Even if the length is different the first digits are indicative of the belonging of a code. For example 1234 and 12345 are “family”-their first 4 digits match.
Just to give you an example of what is desired:
<o></o>
Category____ Code
2200 ________22002
2323________ 232347
<o></o>


So, the loop should do the following:

• Compare the first string from the “Category” column to each and every entry on the right, if a match exists (we have no match here for 2200) write “ok” next to it.
• Next trim one digit from the right from every string in the “Code” column.
• Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200)
• Write “ok” next to it

Now the loop goes to the second string from “Category” column and for this one we will have to trim 2 digits from the right of each string in “Code” column to achieve the result (2323=2323) and so on.
<o></o>
Any ideas would be greatly appreciated.

 

[mikerickson]

<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=85><b>B</b><td align=center width=85><b>C</b><td align=center width=85><b>D</b><td align=center width=85><b>E</b><td align=center width=85><b>F</b><td align=center width=85><b>G</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">category</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">D/C</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">code</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">D/C</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2104</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">D</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">134832</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">not</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2203</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">702121</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">1348</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">210437</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">D</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">not</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">70213</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">D</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">702203</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7022</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">202396</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">C</FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>B4:B8<td align=center>B4 <td align = left >{=IF(ISNA(MATCH(C4&"*",$F$1:$F$10&"",0)),"not",IF(D4=VLOOKUP(C4&"*",$F$1:$G$10&"",2,FALSE),"OK","not"))}</tr>
<tr><td colspan=3 align="center">Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac). </tr></table>

 

[rinser]

Hi mikerickson,

I have used your formula and achieved part of the goals. There is one more step I cannot implement. Unfortunately I don't know how to insert part of an excel table as you did. Please tell me how you did it

 

[mikerickson]

To display a spreadsheet on the board, you could use Jeanie's Excel HTML utility from here. http://www.excel-jeanie-html.de/index.php?f=1

 

[rinser]

Thanks mikerickson for your interest and assistance.
The formula for some reason does not work. The result is always "not". I have to admit it is beyond me. I have uploaded the file to rapidshare because I could not for some reason upload it here. Please advice.

http://rapidshare.com/files/208431120/jeanie.gif.html

 

[mikerickson]

That site seems to be asking me for money to download your file.

 

[rinser]

Hi again,
I uploaded the file to another site, hope this is working.
http://www.filefactory.com/file/af5d1ha/n/Sample3_xls
Thanks again.
P.S. In the previous link when it takes you to their page there are 2 gauges, the one on
the left is for free users, it should work.

 

[rinser]

Thank you mikerickson for your input!

If anyone has any ideas about how this can be put in a VBA module please let me know.

Thanks again everybody for your time!