Compare, truncate, compare again, write (VBA)

R

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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
mikerickson

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,348
This formula should do what you want without VB
=IF(SUMPRODUCT(--(A2&""=LEFT(B2:B5,LEN(A2)))),"OK","not")
 
Upvote 0
R

rinser

New Member
Joined
Mar 10, 2009
Messages
9
SteveO59L said:
Why not just compare the first 4 characters ?
Click to expand...

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
mikerickson

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,348
Looping is not needed. The formula will return the result that you want.
 
Upvote 0
R

rinser

New Member
Joined
Mar 10, 2009
Messages
9
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
mikerickson

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,348
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
R

rinser

New Member
Joined
Mar 10, 2009
Messages
9
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
mikerickson

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,348
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
R

rinser

New Member
Joined
Mar 10, 2009
Messages
9
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
You must log in or register to reply here.

Similar threads

kelly mort
  • Solved
VBA code to set page headers in a special way
Replies
6
Views
233
DanteAmor
DanteAmor
C
  • Solved
VBA to Compare two different worksheets and copy missing rows
2
Replies
10
Views
301
cscotty
C
S
  • Question
VBA Custom function to replicate INDEX MATCH MATCH with predefined fixed arrays
Replies
3
Views
278
DanteAmor
DanteAmor
O
  • Solved
VBA extreme newbie limping through code, vlookup issues
Replies
6
Views
238
DanteAmor
DanteAmor
C
  • Question
VBA for Text to Columns with fixed width using list of widths
Replies
1
Views
165
Alex Blakenburg
A

Forum statistics

Threads
1,191,177
Messages
5,985,131
Members
439,941
Latest member
robertv13

Share this page

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
Top