Compare, truncate, compare again, write (VBA)

rinser

New Member
Joined
Mar 10, 2009
Messages
9
Hi guys,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
Category____ Code
2200 ________22002
2323________ 232347
<o:p></o:p>


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:p></o:p>
Any ideas would be greatly appreciated.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
This formula should do what you want without VB
=IF(SUMPRODUCT(--(A2&""=LEFT(B2:B5,LEN(A2)))),"OK","not")
 
Upvote 0
Why not just compare the first 4 characters ?

The problem is "Category" column has sometime 3 or 5 digits. That is why probably the solution with loops is more appropriate. (I am newbee..)

Thank you Steve, it's quite a nice formula but I am not quite sure how to use it to simulate loops.
 
Upvote 0
I am sorry I did not give you all the details. The reasoning was that if I would give you all the case scenario people would run away :(<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Fortunately it is not the case.<o:p></o:p>
<o:p></o:p>
The earlier presented data sets have less information layers than in reality. I try to put everything in below:<o:p></o:p>
<o:p></o:p>
<o:p> </o:p>
I have two tables (each with 2 columns) with numbers stored as text on two different sheets.<o:p></o:p>
The first one is from product code and D/C (disount category) and the second is from product category and D/C.<o:p></o:p>
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. If the ranges prove to be family the next comparison is for D/C. Only if the "family" and D/C matches can we put "compliant" in the next cell.<o:p></o:p>
Just to give you an example of what is desired:<o:p></o:p>
<o:p> </o:p>
Category_____D/C________Code______D/C<o:p></o:p>
2200________2__________22002______2<o:p></o:p>
2323________5__________232347_____2<o:p></o:p>
<o:p> </o:p>
So, the loop should do the following:<o:p></o:p>
  • 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) then compare the D/C and if both match write “ok” next to it.<o:p></o:p>
  • Next trim one digit from the right from every string in the “Code” column.<o:p></o:p>
  • Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200) then compare the D/C and if both match write “ok” next to it<o:p></o:p>
  • Write “ok” next to it<o:p></o:p>
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) but because the D/C does not match it will go as non compliant and so on.<o:p></o:p>
<o:p> </o:p>
Again I am sorry I did not put all the info from the beginning.<o:p></o:p>
 
Upvote 0
Does the formula return the result you want?
If not, what data gives a bad result and what result do you want from that data.


Looping is not needed to do what you want.
 
Upvote 0
Thanks again mikerickson!

I have applied the formula and for some reason it sometimes performs and at other times not. Something like it sees 7021 as being related to 702121 and then next to it 7022 doesn't seem to have anything to do with 702203. But I need to also check for D/C as well.
Then there is another...problem. My boss is expecting to see a tool/software where a button is pushed and all the necessary checks performed and a report printed with the results......yep...
 
Upvote 0
On further review, I realize that I'm not sure if the Category or the Code is being checked. This might help in either case.
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=45><b>A</b><td align=center width=61><b>B</b><td align=center width=45><b>C</b><td align=center width=61><b>D</b><td align=center width=45><b>E</b><td align=center width=55><b>F</b><td align=center width=45><b>G</b><td align=center width=50><b>H</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">test of</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><td bgcolor="#FFFFFF" > <FONT color="#000000">test of</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">category</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">category</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">code</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">code</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2104</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">134832</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">not</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2203</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">702121</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">1348</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">210437</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7021</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">702203</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">OK</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">7022</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">202396</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">not</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>B3:B7<td align=center>B3 <td align = left >=IF(SUMPRODUCT(--(D3&""=LEFT($F$3:$F$9,LEN(D3)))),"OK","not")</tr>
<tr><td>H3:H7<td align=center>H3 <td align = left >=IF(SUMPRODUCT(--($D$3:$D$7&""=LEFT(F3,LEN($D$3:$D$7)))),"OK","not")</tr></table>
 
Upvote 0
Hi mikerickson,

Actually both Category and Code will be checked in both directions in the end.
The problem remains with the second and forth columns (D/C), they need to be compared as well.
I have come to some code but it's not working and it is incomplete, does not check one after another (looping). If you have any ideas, please share ;)
Here is the code:
Code:
' Find out if each Category is in the Code list<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
' Result is Yes if at least one exists<o:p></o:p>
Sub MatchLists(Category As Range, Code As Range, Result As Range)<o:p></o:p>
<o:p></o:p>
Dim c1 As Range<o:p></o:p>
Dim c2 As Range<o:p></o:p>
For Each c1 In Category<o:p></o:p>
    Dim thisCategory As String<o:p></o:p>
    Dim thisDC As String<o:p></o:p>
<o:p></o:p>
    Dim thisLength As Long<o:p></o:p>
    thisCategory = c1.Cells(1, 1).Value<o:p></o:p>
    thisDC = c1.Cells(1, 2).Value<o:p></o:p>
    thisLength = Len(thisCategory)<o:p></o:p>
    Dim Found As Boolean<o:p></o:p>
    Found = False<o:p></o:p>
    For Each c2 In Code<o:p></o:p>
        Dim thisCode As String<o:p></o:p>
        thisCode = c2.Cells(1, 1).Value<o:p></o:p>
        If (thisLength >= Length(thisCode)) Then<o:p></o:p>
            ' note the change to this test<o:p></o:p>
            If (thisCategory = Mid(thisCode, 1, thisLength) And _<o:p></o:p>
                thisDC = c2.Cells(1, 2).Value) Then<o:p></o:p>
                Found = True<o:p></o:p>
                Exit For<o:p></o:p>
            End If<o:p></o:p>
        End If<o:p></o:p>
    Next c2<o:p></o:p>
    If (Found) Then<o:p></o:p>
        Result.Cells(c1.Row, 1).Value = "Yes"<o:p></o:p>
    Else<o:p></o:p>
        Result.Cells(c1.Row, 1).Value = ""<o:p></o:p>
    End If<o:p></o:p>
Next c1<o:p></o:p>
If (Found) Then<o:p></o:p>
        Result.Cells(c1.Row, 1).Value = "Yes"<o:p></o:p>
    Else<o:p></o:p>
        Result.Cells(c1.Row, 1).Value = ""<o:p></o:p>
    End If<o:p></o:p>
Next c1<o:p></o:p>
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top