Compare Numbers Strings Using VBA (multiple criteria)

[rinser]

Hi guys,

I have two tables (each with 2 columns) with numbers stored as text on two different sheets.
The first one is from product code and D/C (disount category) and the second is from product category and D/C.
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.
Just to give you an example of what is desired:

Category_____D/C________Code______D/C
2200________2__________22002______2
2323________5__________232347_____2
23231________5_________2323_____2

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) then compare the D/C and if both match 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) then compare the D/C and if both match write “ok” next to it
• 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) but because the D/C does not match it will go as non compliant and so on.
Please note that strings can be of different sizes in both directions.

Any help or suggestions will be appreciated.

[Kenneth Hobs]

Welcome to the forum!

It will take a bit of thinking to see what you need. If you post a sample xls, it will help us help you more easily. Click the Manage Attachments button below your reply to attach the file.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim sh As Worksheet
Dim cell As Range
Dim FirstAddress As String
Dim LastRow As Long
Dim i As Long

Set sh = Worksheets("Sheet2")
With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

Set cell = sh.Cells.Find(.Cells(i, TEST_COLUMN).Value & "*", LookIn:=xlValues)
If Not cell Is Nothing Then
FirstAddress = cell.Address
Do
cell.Offset(0, 2).Value = "Compliant"
Set cell = sh.Cells.FindNext(cell)
Loop Until cell Is Nothing Or FirstAddress = cell.Address
End If
Next i
End With

End Sub
[/vba]

[rinser]

Here is the description (it's quite long )
It is not necessary to go through all the steps, just a few would suffice, I would just need to understand how it works.

I have two ranges of numbers stored as text.
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.
Because the length of the strings is different we will have to trim them in both directions and make the comparisons.

In the first cycle a Compass whole NNGs is compared to DBase range of NNGs first whole then trimmed by up to 2 digits from the right.


1. Compare first NNG from Compass range to each NNG from DBase
2. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell
3. If no match is found compare Compass NNG to DBase range with a digit trimmed from the right of DBase NNGs.
4. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.
5. If no match is found compare Compass NNG to DBase range with two digits trimmed from the right of DBase NNGs.
6. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.


Next step is to trim one digit per cycle from Compass NNG and compare to whole NNGs from DBase range.


7. Compare first NNG from Compass trimmed by one digit from the right to each of the range of whole NNGs from DBase
8. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.
9. Compare first NNG from Compass trimmed by two digits from the right to each of the range of whole NNGs from DBase
10. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.


In the next step one digit is trimmed from the right of Compass range NNGs and resulting NNG is compared to DBase NNGs trimmed from the right by 1 to 2 digits


11. Compare first NNG from Compass trimmed by one digit from the right to DBase range with a digit trimmed from the right of DBase NNGs.
12. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.
13. Compare first NNG from Compass trimmed by one digit from the right to DBase range with two digits trimmed from the right of DBase NNGs.
14. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.


Last is to trim Compass NNG by 2 digits and compare to DBase NNGs trimmed by 1 digit


15. Compare first NNG from Compass trimmed by two digits from the right to DBase range with a digit trimmed from the right of DBase NNGs.
16. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.
17. Compare first NNG from Compass trimmed by two digits from the right to DBase range with two digits trimmed from the right of DBase NNGs.
18. If a match is found next compare discount category, if both match enter a message “Compliant” into a designed cell.

Here is a more simple scheme for the above:

Compass DBase
Compass DBase-1
Compass DBase-2

Compass-1 DBase
Compass-2 DBase

Compass-1 DBase-1
Compass-1 DBase-2

Compass-2 DBase-1

Compass-2 DBase-2

Here is the file:

Attachment 337

Hope it will make one interesting piece to study for a lot of people.
I guess it's not every day that people come to solve this sort of things in VBA

[rinser]

Anyone any ideas how this can be achieved? :/

[mikerickson]

Cross posted from
http://www.mrexcel.com/forum/showthread.php?t=376580

[mdmackillop]

Thanks Mike.

[rinser]

Hi guys!

Indeed I posted my question on another forum as well because the issue is quite complex so I did not have high hopes. Even if I cross-posted I did not get a workable solution on any and was not aware I was doing something wrong I still need a workable solution in VBA (this module will be part of a bigger one).

Thank to everyone who took interest to look at it so far!

[rinser]

Hi guys again,

I have come to a solution with help (being a beginner I couldn't have done it myself) and here it is.
Attachment 453

I have applied the algorithm to my data and there is an issue - in the NNG2 column it looks at the NNGs' first digits from the left and once it finds a match it checks for DC and writes the result and goes on to the next in NNG1 column on the left. The problem is that many times we have an exact match few lines lower in the NNG2 column but it doesn't get to check those as well. This is better exemplified in the attached file, for last rows the result is non-compliant when it should have been compliant.

I would really appreciate if someone could look at the code and maybe improve or come with suggestions. Thanks again for your patience.

[mdmackillop]

A slightly different methodology. Copy and modify the data in a "helper" range and loop through comparing the data. The same comparison routine can be reused. Three tests are run in the example.
I've made your range names dynamic to simplify the coding.

[rinser]

Hi mdmackillop,

Thank you for the input but it still isn't performing as desired. It is not comparing everything required and since this would be used by people with very basic Excel skills I would like it to operate at the push of a button.
Unfortunately it is beyond my present VBA skills to make it work as it should.

Thanks again!

[mdmackillop]

This should contain all options, but not thoroughly tested. If you remove the apostophe before a Stop, you can see the comparison data at that particular test.
eg, this will stop afer test 6

[vba]
DoCompare Com2, DB2, 6
Stop

[/vba]

[rinser]

Thank you Malcolm!

The method you proposed is very nice and actually working!

Also thanks to everyone who contributed on the forum!

Cheers!
Sergiu.