+ Reply to Thread
Results 1 to 30 of 30

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

  1. #1
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,768

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

    I would not use a macro - I would use formulas:

    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

    In BF2, for example, use

    =IF(AND(COUNTIFS(C:C,C2,AF:AF,AF2)=1, COUNTIFS(C:C,C2,AG:AG,AG2)=1),IF(COUNTIFS(H:H,H2,D:D,D2)=1,"OK","Fail"),"Fail")

    2) I'm not sure if I know what you mean.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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.

  4. #4
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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

  5. #5
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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.

  7. #7
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    Dear Lewis,


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

    Lax

  8. #8
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    Added reputation lewis.thanks.

  9. #9
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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

  10. #10
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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

  11. #11
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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

  12. #12
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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

  13. #13
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

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

    Lax

  14. #14
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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.

  15. #15
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

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

    Lax

  16. #16
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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

  17. #17
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    Dear LJ,

    Im attaching the Word doc for more info

    Kind Regards,
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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.

  19. #19
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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

  20. #20
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    Try this for BF:

    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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

  22. #22
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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

  23. #23
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

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

    Kind Regards,
    lax

  24. #24
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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.

  25. #25
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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?

  26. #26
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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
    Attached Files Attached Files

  27. #27
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

    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

  29. #29
    Forum Contributor
    Join Date
    02-19-2014
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    308

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

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

  30. #30
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. !! Help with huge amount of data - allocation
    By mariosmk555 in forum Excel General
    Replies: 3
    Last Post: 03-23-2014, 02:32 PM
  2. [SOLVED] Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is N
    By Researcher1111 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-15-2013, 10:20 PM
  3. Need a formula to Pass/Fail data entered based on several variables....
    By smurf0617 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-21-2013, 01:10 PM
  4. Replies: 5
    Last Post: 03-16-2010, 07:05 AM
  5. Matching 2 columns of data Pass Fail or Blank
    By testpilot in forum Excel General
    Replies: 1
    Last Post: 03-05-2009, 09:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1