Mark Pass/Fail Using Vba for the Huge amount of data

Hi All,

I'm Working on the below macro and i have come up with the below questions.Kindly help.

1) Macro 1 (use column BF for the message)
a.if there are dupe vendor numbers(column C), and the corresponding bank records(column AF,AG) are unique, then it's ok, else fail
b.if there are dupe vendor names (column H), and the corresponding account group (column D) is different, then it is ok, else it's fail

2) Macro 2 (use column BG for the message)
a. if there are dupe vendor names (column H), if the vendor account group (column C) is either ZM05 or ZM14, then the country (column P) associated to it should be the same as the the country (column P) associated to ZM01. If fail, then mark all the lines failed

3) This is not a macro. I made a TRIM check on column BE, but it doesn’t work for some reason. I guess it has something to do with the “convert to numbers” thing



Lax

Hi Lax,

These are the rules I used for Macro1:

Please Login or Register  to view this content.

These are the rules I used for Macro2:

Please Login or Register  to view this content.

I probably didn't implement exactly what you wanted. Please test, and let me know what changes you need.

I was unable to do anything with the TRIM check on column' BE'. All I see in the column is =#VALUE! as the formula and the value FAILED.

Lewis

Macro code follows:

Please Login or Register  to view this content.

Dear Lewis,

Thx for the quick resolution.



There are some problems with it. It is probably because I didn’t give you a proper explanation, I am sorry



1) Macro for BG:



It works fine. But there should be an additional condition.

I won’t be able to explain it, but I will try to represent it on this picture.



On this picture below all the lines should be OK, because there is also “ZM01” record where the country is “GB”
On the original one both top2 were “US”

image002.png


2) Macro for BF:



This one is completely wrong, and it is purely my fault, I am sorry again.



This is what I would need for BF:

For the same vendor names, the banking details (AF,AG) should be unique.


Note: I have attached the macro tool with your code applied.



Please help me in the final small endorsement.


Thanks in advance Lewis,
Lax

Due to size contrains am unable upload the file.But the same file has been uploaded in the post#1 of this same page.

Thanks in advance,
Lax

Hi Lax,

I think I did what you want. In the future it would be a lot easier for me, if you had sample output. That way I would know if my algorithm is correct, by comparing my results to your sample output.

Please let me know if you need any more changes.

Lewis

Code follows:

Please Login or Register  to view this content.

Dear Lewis,


Thank you so much and I tested it.it is working like a charm.sorry for the delayed reply.

Lax

Added reputation lewis.thanks.

Dear LJ,

Thx I checked. The BG works fine.



Can you please check the BF again though?. I am not sure if it is working as intended. I try to explain here below.
image002.png


Lax

Hi,

Here are the rules I follow when calculating 'Failed' or 'OK' for Column 'BF'. Pass 1 processes all the data lines. Pass 2 processes all the data lines after Pass 1 has completed. Pass 2 WILL NOT change a 'Failed' to 'OK'.

Please Login or Register  to view this content.

I will gladly make the changes you want, but before I can do that, I need to see where they belong with respect to the 'Failed/Ok' rules.

Lewis

For the same vendor names (column H), check if the bank details (column AF and AG) are the same. If yes, mark all of them failed, if not “ok”

In this example below row 2,3 should be ok, because AF,AG are unique.


I entered some explanation to column BH (this should be the correct status) and BI (why it should be correct.

Attachement.png


Lax

Hi Lax,

I'm sorry but you are still not answering my question.

We need to modifiy the following rules to include your exceptions, before they can be coded.

Please Login or Register  to view this content.

In your example above, rows 2 and 3 FAIL 'Pass2' because the Vendor Name (H) and Account Group (D) are the same.

Lewis

Sure ...LJ ..we can change the rules and code.I'm fearing ..but my output be like the snapshot.that is expected friend.

Lax

I did the best I could with the information you gave me. I am afraid that we may have unanticipated side effects. Please review the results carefully.

Please Login or Register  to view this content.

New Code for 'BF' follows (changes in red):

Please Login or Register  to view this content.

Thank you LJ. Thanks for the multiple amendments.GOd bless you.Added rep..

Lax

Dear LJ,

I had a check with the rules you mentioned..and i have come up with the remarks below(Highlighted).

Please Login or Register  to view this content.

After analyzing i found,There are three more i need to Amend, and that’s it.





1. Column BF: this is what we talked about in our previous email. This is what you are working on. Once again. The cycle should only go through the same vendor names (column H), and check the corresponding bank details (AF,AG). If the AF,AG are unique, then “OK”, else “FAILED”
There should be an exception. Blank bank details are acceptable. So blanks and unique AF,AG records for the same vendor names are “OK”, otherwise “FAILED”

2. Column BH: dupe check

If C,D,H,AF,AG are the same, then it Is a dupe (“failed”)

3. Column BI: trim check

If the any of the cells in one row has an extra space either at the beginning or at the end, then it is “failed”


Kind Regards,
Lax

Dear LJ,

Im attaching the Word doc for more info

Kind Regards,

Here are the finished items for BH and BI. Please note that you may want to replace the BI routine, with a routine that removes leading and trailing spaces.

At the top of the Macro YOU MUST install the line in red below to replace a SIMILAR line.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I'm still a little confused about BF requirements, per your Word doc in post #17 above.

1. The original rules performed a second pass comparing columns H and D. The Word doc says column 'D' is no longer required. I will omit the second pass comparing H and D unless you specify otherwise.

2. I will use the rules specified in the Word doc for Column BF.
I am still unclear about BLANKS in AF and AG. For example in the word document the blank AF and AG FAIL for 'Adept Computer Services' in lines 24 because lines 25 and 26 are duplicates.


------------
I just want to verify that I am doing the right thing.

Lewis

Try this for BF:

Please Login or Register  to view this content.

Dear LJ,

Thank you very so much..

BF,BI works as intended.

As for BH, it doesn’t.
This is what is needed: dupe rows check If C,D,H,AF,AG are the same, then it Is a dupe (“failed”).
Snapshot for the reference.

Attche1.png

Also Kindly please tell me,how i can achieve the BK,BL,BM (Details attached in the Word Doc Post# 17)

Eagrly waiting for the Reply.



Lax

I will work on BH.

I am having problems with BK, BL, BM because they are more complicated than they appear to be. If you could provide a few pass fail samples it would be appreciated.

Lewis

Thanks Boss..Sure i will prepare and give you the sample.

Kind Regards,
lax

Please check 'BH' again. I double checked and I get a failure as expected on the Yellow Highlights in #21 above as expected. Here is the code again. No changes were made to the code.

Please Login or Register  to view this content.

BJ, BK, BL Question:
if US then VAT can be blank
if GB, then Tax Number can be blank
What if there is a different country code ?
What if there is no country code?

Dear LJ,

Thank you so much.Here is the answer for your questions..

I know this is one a very difficult task. I am not sure I can explain it better.
I gave you some examples

But this is the basic rule. If you are confused about my red explanation, try to understand this.

BK = credit number check = we just need to make sure that the duns number are unique for ZM01,ZM07,ZM11. ZM05 and ZM14 can inherit the credit check number from ZM01 (there are cases when there are multiple ZM01 or ZM07 or ZM11 with the same DUNS, because of the different bank details. That’s why we need to examine the bank details as well)

BL = Tax number2 check = it is only relevant for the “US” records. It should be unique for ZM01,ZM07,ZM11. ZM05 and ZM14 can inherit Tax number2 from ZM01 (there are cases when there are multiple ZM01 or ZM07 or ZM11 with the same tax number 2, because of the different bank details. That’s why we need to examine the bank details as well)

BM = Vat number check = it is only relevant for the “GB” records. It should be unique for ZM01,ZM07,ZM11. ZM05 and ZM14 can inherit vat number from ZM01 (there are cases when there are multiple ZM01 or ZM07 or ZM11 with the same VAT number, because of the different bank details. That’s why we need to examine the bank details as well)


Lax

Hi,

See the attached file that contains very preliminary results for BK only. The rules are so complicated, that I would like to make sure we have the basic things correct before we attempt more complicated rules.

The attached file attempts to implement:
a. ZM01, ZM07, and ZM11 only
b. All single line failures (e.g. No Credit Check, No VAT, No Tax Number 2)

It may take several attempts to get this correct. Once we do that, I will implement the 'ZM05' and 'ZM14' inheritance.

I have attempted to implement the code using the following rules:

Please Login or Register  to view this content.

I will start on BL in the same manner.

Lewis

Thanks for the "BK" Lewis..Others are just with different variables..


Please check it now if you understand it. If not, I will give you a different explanation.

To reduce the confusion i have given some more details Lewis..

But this is the bottomline:

>> *BK = credit number check =* we just need to make sure that the duns
>> number are unique for ZM01,ZM07,ZM11. ZM05 and ZM14 can inherit the
>> credit check number from ZM01 (*there are cases when there are
>> multiple ZM01 or
>> ZM07 or ZM11 with the same DUNS, because of the different bank details.
>> That’s why we need to examine the bank details as well)*
>>
>> *BL = Tax number2 check =* it is only relevant for the “US” records.
>> It should be unique for ZM01,ZM07,ZM11. ZM05 and ZM14 can inherit Tax
>> number2
>> from ZM01 *(there are cases when there are multiple ZM01 or ZM07 or
>> ZM11 with the same tax number 2, because of the different bank
>> details. That’s why we need to examine the bank details as well)*
>>
>> *BM = Vat number check =* it is only relevant for the “GB” records.
>> It should be unique for ZM01,ZM07,ZM11. ZM05 and ZM14 can inherit vat
>> number from ZM01 *(there are cases when there are multiple ZM01 or
>> ZM07 or ZM11 with the same VAT number, because of the different bank
>> details. That’s why we need to examine the bank details as well)*


Thanks ,
Lax

Thanks LJ..I have done the remaining part..

Your welcome. I'm glad you are able to to the rest yourself. I don't mind doing the work, but it makes me feel better when someone else can use my code as a baseline.