Compare Numbers Using VBA

This looks like a perfect application for a Collection. Collections can be keyed or not and can store any object that you can conceive of. Here's a couple fo pointers.

Say you created a class module called myArray that contains the things that you want the retain in a key list. Then you can construct a Collection

Please Login or Register  to view this content.

and repeating create new instances of myArray to put in the basket using a key

Please Login or Register  to view this content.

You can then access the Basket by key using

Please Login or Register  to view this content.

The Add and Item functions will throw an error if you try to store a duplicate or retrieve an item for which there is no key, so you will want to use On Error Resume Next to do your own error handling.

Hope this helps. Let me know if you need more.

I think I undersand where you are going. You can empty the basket one of two ways. First, you can just destroy it and create it again:

Please Login or Register  to view this content.

or you can remove all of the items in the collection

Please Login or Register  to view this content.

You might use the second one if for some reason you wanted to keep some of the items in the collection around. In that case, you would need a slightly different loop, but you should get the item.

Let me know if you need anything else.

Hi blane245,

Thank you again for your advice!
Here is an example of what I have to do. This is a simplified example as the real database is large and cumbersome to reproduce here.

The idea is I have these two databases (1 and 2) and I have to know that for each code in D1 there is a corresponding correct code and attributes in D2.

To make process shorter I propose to first look only at the Code A and Code B and store matches in a temporary place (basket) and then look and compare the rest of the attributes (in our case D/C and Date).

Please note that from the Database 1 (D1) we have only the Code A and D/C attributes whether for D2 we have the Code, D/C and Date.

We go from these two data sets(tables) in Excel spreadsheets. They are extracted from some sql databases and converted to the same format. The column "Compliance" should be filled in with either "Yes" or "Now" depending on whether we found a corrresponding match for respective Code A.

So we go from these two data sets. (I used underline to keep it in some readable form).
|
_Database 1_______|___Database 2_______________________Compliance
Code A_____D/C___|____Code B_____D/C_______Date________Yes/No ?
456________1____|____456_________3________04 July
214________2____|____456_________2________23 August
157________2____|____456_________1________01 January
365________3____|____456_________1________20 May
125________1____|____365_________2________22 March
148________2____|____125_________1________23 April
First thing is to create two temporary "baskets"
Second we put first code from D1 and corresponding D/C into Basket1.
Next we take the code from Basket1 and compare it to all the codes (codes only) from D2. All the matches are put into Basket2 together with accompanying D/C and Date.

After comparing the Codes we will come to this shorter selection:

|
Basket1__________|_____Basket2
|
Code A_____D/C___|___Code B______D/C______Date
456________1____|______456_________3________04 July
_________________|_____456_________2________23 August
_________________|_____456_________1________01 January
_________________|_____456_________1________20 May

Next step is to compare also D/C and leave only those matching:

|
Basket1 | Basket2
|
Code A_____D/C___|___Code B______D/C_______Date
456________1____|___456_________1________01 January
________________|____456 _________1________20 May

And the last step is to compare the Dates and leave only most recent.

|
Basket1 | Basket2
|
Code A_____D/C___|___Code B______D/C_______Date
456________1____|____456_________1________20 May

And finally because we found our record and all the attributes match we put "Yes" into Compliance column.

Basically this is what this algorithm is about. Please tell me what you think about all this.

That would be great! shg, thanx for the suggestion.

Hi guys,

Sorry I did not come back faster, it is just I was unsure about how the requirements will change and they did change a bit. I dropped the "temporary array" thing altogether as it is not needed anymore. What I need instead is this:

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:

Category_____Code
2200________22002
2323________232347

So, the loop should do the following:
1. 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.
2. Next trim one digit from the right from every string in the “Code” column.
3. Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200)
4. 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.

Any ideas would be greatly appreciated.

The following will give you a "Yes" result of the Category appears at least once in the Code list. You provide the routine with the Category, Code, and Result ranges from your worksheet.

It uses the Mid function to check the beginning of each Code for the desired Category.

If you need to know how many Codes match the category, then you could count them rather than just return Yes is you get any match. Also, once you have a match, you can find out what are the additional characters in the Code, if you need to do something with them.

This will run a bit slowly if you have lots of categories and codes, but it will get the job done.

Please Login or Register  to view this content.

Thank you very much blane245!

I am sorry but I have now one more layer of information to deal with. If you are still with me please look at this new requirement.

I try to put everything in below:


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

So, the loop should do the following:
1. 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.
2. Next trim one digit from the right from every string in the “Code” column.
3. 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
4. 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.

Again I am sorry the details of the case have changed a bit.

In this case, I would make some slight changes to the code. First, let me assume that the Category and Code range variables in the routine have two columns rather than one column each. This make the code change simple. You just add an additional test to the filter and you have it.

I am using string comparison here. You could also use numeric comparison if that make more since.

Please Login or Register  to view this content.

No problem with asking question.
First, I work this as a routine that you would call and pass specific ranges from our worksheet. For example, it could be called like this

Please Login or Register  to view this content.

Of course, Sample1 would have to be open, the Category value would be in cells A1:A10, the Codes in cells B1:B10, and the results would be put into C1:C10. All of these would be on Sheet1.

This call the MatchLists would be in another VBA function or sub. For example, you could put it in a procedure that handles a button click.

The variables c1 and c2 are Range variables that are actually pointers to each cell in the Category and Code ranges. This is what the For Each does. Actually each will be a cell, so the expression c1.Cells(1,1).Value gets the value of that cell.

Your statement

Please Login or Register  to view this content.

actually assigns thisCategory as a pointer to a range variable and will cause an error as thisCategory is declared as a String variable. There are many appropriate uses of the Set statement, but this is not one of them.

I hope this clears things up a little. Keep asking questions as you need to.

Thanks again blane245,
Do you know how to paste/upload an excel file/table unto forum here?
I feel it would be much easier if I could upload part of my data and discuss it here..

Sure. If you use "Go Advanced" on your reply you will see a paper clip that allows you to attach files. First you select the file and upload in the dialog. Then you use the paper clip again to select of the files you've uploaded to attach.

Thanks again blane245!
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:
Sample3.xls

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

Hang in there a bit for me. I've had some high priority interrupts. Let me ask some questions about your data.

On row 7 you say non compliant because DC differs. I see A9=78569, B9=2 C7=7856, D7=2. Why is this non compliant?

On row 9 you say compliant. I see A7=15874, B7=5, C9=1587, D9=2. Why is this compliant?

On row 11 you say compliant. C11=56708 which doesn't match anything. Wy is this compliant?

On row 12 you say compliant. A6=345, B6=5, C12=3456, D12=2. Why is this compliant?

I need to understand these before I can proceed.

Hi blane245! Glad to hear from you again!

First we refer as compliant or non compliant only for column A at this stage. In other words we compare column A to C not C to A. I will have to compare C to A but not in this module and once the algorithm is built it can be put to work backwards as well.

On row 7 you say non compliant because DC differs. I see A9=78569, B9=2 C7=7856, D7=2. Why is this non compliant?
Actually next to A9 it says compliant in G9.

On row 9 you say compliant. I see A7=15874, B7=5, C9=1587, D9=2. Why is this compliant?
It says non compliant in G7.

On row 11 you say compliant. C11=56708 which doesn't match anything. Wy is this compliant?

It says compliant for A11 (14780 & 2) it matches C8(14780 & 2).

On row 12 you say compliant. A6=345, B6=5, C12=3456, D12=2. Why is this compliant?
It is compliant for column A (2457 & 5) matches C5 (24572 & 5).

The match shouldn't necessarily be on the same row, actually its close to never on the same row. The first list has 5000 entries and the second 2000.
This happens because many entries on the left column have sometimes just one truncated correspondent on the right side (a catch-all number).
If we were to have 12345, 12346, 12347, 12348 on the left and 1234 on the right this would mean that all those 4 on the left are compliant - every one of them has that truncated correspondent on the right.

It can be disconcerting in the beginning but once you start seeing the picture and what is to be achieved it begins to be quite simple.

Please feel free to ask any questions about all this. Thanks again for your interest.

OK. I see what I did wrong. Attached is a cut at the Compass->DBase comparison that matches your results (almost, except on row 11). The code is attached to the button labeled Compass->DBase.

Note that the Compass and DBase columns must end with a blank row for this to work. That determines the end of the lists. If you have some other delimiter, use it.

Also note that I have defined three names in the workbook: Compass, DBase, and Compliance.

You could copy the loops and exchange the rolls of Compass and DBase to get the reverse match.
Copy of Sample3.xls

Thank you blane245!
I will look tomorrow into the file and maybe test and adapt it to the data.
Thanks again and will come back and let you know how it went.

Hi again,

I was thinking that we have two subprocedures
1. the matching filter NNG1 smaller or equal than NNG2
2. the matching filter NNG1 larger than NNG2

And maybe instead of this two it would be better to implement three as follows:
1. the matching filter NNG1 equal to NNG2
2. the matching filter NNG1 smaller than NNG2
3. the matching filter NNG1 larger than NNG2

What do you think?
Thanks again

Hi again blane245,

I have made some changes to the code trying to nest the following subprocedures into each other so that the first is executed first and only if the result is negative the second is executed and so on.

1. the matching filter NNG1 equal to NNG2
2. the matching filter NNG1 smaller than NNG2
3. the matching filter NNG1 larger than NNG2

Surprisingly it still works but does not yield the results yet. I am attaching the file with the changes in code:
Copy of Copy of VBA Solution.xls

Please note that I have added a few rows to better exemplify where the problem is. Please look at the expected results in blue, those are the problematic ones.
Thanks again, hope to hear from you soon

Hi blane245,

I have figured out why the set of ranges wouldn't work in my data set, I was omitting defining the names (of ranges). This is not an issue anymore.

The problem still persists with the comparison algorithm the one described in my previous 2-3 posts. I am trying to think of something and made some changes but nothing works so far.

So, if you have the time to look at it please post here any ideas.
BTW the module's code is neat and elegant IMHO.
Thanks again!

Hey, somehow I haven't been getting notifications on this thread. Sorry for not answering, but I didn't know you had replied. Give me a little while to check through your messages and I'll get back with you.

After looking at this briefly, what is needed is to look through the entire list of other NNGs and use the best match found. Thus, if there is an exact match it will be used. Right now, the routine I gave you does a first match, so it wouldn't detect a better second match.

This requires changing the code that I gave you so that all NNGs are searched and the best matching row is tracked. Then is a match can is found, check for the DC match on those rows.

Do you follow, or should I change the code that I sent last time.

Hi again,

I have posted a new thread regarding this module and it has been solved today.

http://www.excelforum.com/excel-prog...a-compare.html

Thank you very much for your input on this issue! Guess I'll have some more questions regarding other modules so your help will be welcome anytime!

Cheers,
Sergiu.