Looping works wrong (VBA compare)

[rinser]

Hi All,

I have two ranges each in 2 columns (total 4 columns). First range is named Compass and it contains columns NNG1 and DC1, the second named DBase contains columns NNG2 and DC2. I need to compare each NNG1 from Compass range to all NNG2s from DBase and if NNG1 is contained within NNG2 or NN2 is contained within NNG1 and (mandatory) DC1=DC2 then print result to a third range called Compliance.The code does not perform as it should.


Please find attached the proposed file illustrating the problem, for the rows in blue the solution is incorrect, the code does not compare all the rows, once it finds something similar to what it is looking for, it stops and does not look further:
VBA Solution.xls
If someone could look at the code and advise I would really appreciate it!
Thanks!

[DonkeyOte]

I don't see any rows in blue... do you want to upload again ?

It seems to me your code will stop as soon as it finds the NNG1 value within the string of NNG2 or the NNG2 value within NNG1 (whichever is shorter), however, this would imply that should a better match be available further on in the 2nd set (ie say where DC matched) this would be ignored... a good example would be say

A6: 345 with DC value of 5
C6: 34569 with DC value of 2

Now let's say

C12: 3456 with DC value of 5

The code as is when processing A6 would stop at C6 given left 3 chars match, however, given DC values are not the same this would be deemed non-compliant, however, if you were to continue through all values in C you would find a Compliant match (and smaller residual) in C12

If you post a file where you have variances from expected results that would help clear the waters a little. I suspect you can streamline a little.

[rinser]

Thank you for fast response!

You are right in your observations, the loop stops once it finds something similar to what it's looking for.
Unfortunately the file is quite big and this excerpt is specifically chosen to illustrate this problem. Once it would be made to not choose the first encounter and go as far as C12 the problem is solved. Unfortunately it's not as simple as it seems (at least to me, newbie).
I think a new procedure should be implemented where a decisional process is implemented in which all entries are analyzed and then the proper one chosen. I thought of maybe making it look first at longer entries (more digits) and then at shorter ones as well. I searched for a solution but did't find anything similar. This would make an interesting case study for other people too I guess.

Any input is appreciated!

[DonkeyOte]

Going back to my original point... there are no rows in blue (at least not when I look at it), moreover the results listed in E appear to tie out to your expected results so it's hard to comment.

What are the sizes of each range in reality - ie how many records do you have listed in DBASE listing for example ?

[rinser]

I have in Compass 6000 ranges and in DBase 2100. Sorry I attached a first version of the file, I am attaching the one with the changes:
Copy of VBA Solution.xls
The ranges are stored in the second database using truncation: for 1234, 1235 and 1236 in Compass we can have one entry in DBase 123. In this case 123 is actually correspondent for ten Compass ranges from 1230 to 1239.
Thanks again!

[DonkeyOte]

OK yes the latter file makes more sense.

Given 2 facts:

1 - you MUST process all DB records for each value in Compass Range
2 - the volume of DB records

I would suggest a slightly alternative approach to iterating each DBase record for each Compass record within the VBA... I will post an alternative approach shortly.

[DonkeyOte]

OK, see if the attached helps ?

Note: I altered your Named Ranges so as to limit their size appropriate to records present, they are now defined as:

Name: Compass
RefersTo: =Sheet1!$A$3:INDEX(Sheet1!$B:$B,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

Name: DBase
RefersTo: =Sheet1!$C$3:INDEX(Sheet1!$D:$D,MATCH(9.99999999999999E+307,Sheet1!$D:$D))

The VBA for ref. is below:

Please Login or Register  to view this content.

I hope it helps.

[rinser]

Thank you very much!

I am going to look at it tomorrow and try and understand how it works. Also will do some testing and report back.

Looks impressive! Thanks again!

[rinser]

Hi again,

I have tested the code on a number of ranges and found one strange occurrence. It is shown in the attached file (the same just added a few lines) colored in pink. It shows incorrectly one record as compliant. I have looked into the code but honestly it is beyond me why this is happening.
677579_rinser.xls

Thanks again,
Sergiu.

[DonkeyOte]

Sergiu, I can see the issue but will revise tomorrow as it is late... the result should be Compliant though the matching row should be row 13 rather than 7 with remainder of 1... the code is not handling the fact that a non-compliant remainder may be smaller than the remainder of a compliant row, presumably any compliant DC should supersede non-compliant DC even if remainder is smaller ?

[DonkeyOte]

In the meantime here is a quick alteration for you to test ... I can't do so myself as I'm heading offline

Please Login or Register  to view this content.

[rinser]

Thank you so much DonkeyOte!

I have applied the suggested change and it works!
I did not have yet the time to look into the code and understand it properly but so far it performs correctly which is fantastic!

Thank you very much!
Sergiu.

[rinser]

Hi again,

I have one more question. I have some ranges starting with 0 like 02234, in order to keep them with preceding 0 I have to put them in text format. These are not recognised by the algorithm and not processed. Dou you know what can be done so it can process those as well?

Thank you.
Sergiu.

[DonkeyOte]

Sergiu, are all values formatted as text ?

FYI: the VBA does not evaluate as to whether or not the strings are potentially compliant, this is done within this formula in the Temp Column, ie in the below:

=IF(OR(0+LEFT(RC3,R1C)=R2C,0+LEFT(R2C,LEN(RC3))=RC3),1,""x"")

As you can see the above coerces the strings to numbers which is where the issue is arising... and also when the Compass ID is placed into row 2 in the final column this will be coerced also...

On this basis try altering:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

And subsequently

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If that does not work please provide a sample file so I can test further.

[rinser]

Hi again,

I have one more question, is it possible to build one more logical step into the algorithm so that it performs another check? It turns out that if a perfect match is found for the NNG1 then the cycle should stop and DC comparison should be done and the result printed. For example for A4 (23587&2) there is a perfect match (NNG wise) C15 (23587&5), in such a case the result should be "Non Compliant : DBase row=15 DC mismatch". This is an exception to all the rest of the steps and should precede them. Do you think this can be implemented without altering the rest of functionality?

I am attaching the file with the latest changes:

677579_rinser.xls


Cheers.
Sergiu

[VBA Noob]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

[DonkeyOte]

Sergiu, in lieu of VBA Noobs post - on a scale of 1-10 I'm 10 annoyed...

[VBA Noob]

DonkeyOte,

He's posted on at least 2 other forums as far as I can see

VBA Noob

[DonkeyOte]

Thanks for the heads up -

Sergiu, this may seem like we're throwing our toys around but bear in mind we help in our spare time for free so posting a question in multiple places is a sure fire way to alienate yourself... some forums would ban you for x-posting for this reason. Most of us are members on multiple forums so it's highly unlikely that posts will remain unnoticed, I did run a quick check on MrExcel.com where I am also a member and though you had posted the same question a few weeks ago it was albeit on a different theme (ie formulae based) so I did not consider it a x-post at that time.

You can needless to say consider my subscription to this thread cancelled I'm afraid.

All the best,
DO.

[rinser]

Hi DonkeyOte again,

I have considered how this last requirement can be solved and I think I will go a different way, I will build (hopefully myself this time ) a module that will sort all exactly matching NNG doubles and will leave only one of them that is newer, the table will also have an applied date column.

The solution that you proposed works perfect so far. Only thing is for some data it does not perform as it should due to some formatting problems I suppose. I will have to investigate this and write here.

I should have mentioned that the topic of this thread was initially posted on another thread that unfortunately did not provide much-needed final code.
I am posting the link here:
http://www.excelforum.com/excel-prog...using-vba.html
Special thanks to blane245 for all the help in that thread!

Cheers!

[rinser]

Hi All again,


Regarding the cross-posting issue it did happen but not because of some ill-intended actions. I have never been a member of any excel-related forums up until a few weeks ago and did not know/think of it as I do now that it actually can be a tedious, time-consuming task to solve all sorts of questions people throw. On the other hand I did not know what forum is worth what and from my short experience there are differences. I did not even count on people answering my thread. At the same time I needed an answer to my problem.

From now on I will post all my threads here and should it be the case to post somewhere else I will specify this in the thread and provide the link.

I am sorry for the situation with cross-posting, it will not happen again.
Thanks again for the help!
Cheers,
Sergiu.

[DonkeyOte]

Apology accepted, see below re: revision for exact matches etc...

Please Login or Register  to view this content.

[rinser]

DonkeyOte you are an angel!

Thank you so much for this last input for this module. I surely hope that no more action is needed for this one.

Thank you very much!
Sergiu.

[rinser]

Hi DonkeyOte!

I have tested the module and it performs great!

The only little bug is if the columns are of different lenghts then if an NNG is not found it will say "Non Compliant" and point to the next empty row from DBase. This is not major but if it does not take more than 5 min could you please improve it?

I was also wondering if it is possible to copy the found data from columns NNG2 and DC2 in the result in the Compliance column. Then the result would look like this:
Compliant: DBase row=8: "14780 : 2" remaining characters=0
The reason behind this question is that with a long list you get the result pointing you to a row that is sometimes many scrolls away e.g at row 1400 you get pointed to 4300 in DBase columns and it takes quite a lot to scroll to that row. Another solution to this would be probably to just copy the data to columns F ad G respectively. Whatever is simpler I guess.

If I try to make changes it just stops working...

Cheers,
Sergiu.

[DonkeyOte]

The only little bug is if the columns are of different lenghts then if an NNG is not found it will say "Non Compliant" and point to the next empty row from DBase.

This was an oversight on my part - the temp formulae dimensions should be based entirely off DBase range rather than Comp range.

To rectify, change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

I was also wondering if it is possible to copy the found data from columns NNG2 and DC2 in the result in the Compliance column. Then the result would look like this:
Compliant: DBase row=8: "14780 : 2" remaining characters=0

If I've understood...

Change:

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Note: line breakers added in the replacement code for the Result string.

[rinser]

Thank you very much for the effort and input!
It solved the problem of "pointing to empty row" and also now shows found info in the Compliance column. The only minor thing, again if its not taking much time, is when the result is "Non Compliant : Row X DC differs" the found NNG and mismatching DC is not shown. I have looked into the code and my guess is in the following lines is encluded true/false evaluation:

Please Login or Register  to view this content.

but I don't know how to make it show the info for the negative boolean as well.
Cheers,
Sergiu.

[DonkeyOte]

You are correct that it only displays that info for Compliant rows, to make this display for all rows:

Remove the IIF... so becomes:

Please Login or Register  to view this content.

[rinser]

Thank you DonkeyOte again!

It performs now to its fullest!

Great place this forum and its good people!

Cheers,
Sergiu

[DonkeyOte]

Good, please mark thread as Solved.