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:

  'This puts 'OK' or 'FAILED in Column 'BF' based on the following:
  '
  'Pass 1:
  'Examine the contents of:
  'Column C  (Vendor Key)
  'Column AG (Acct No)
  'Column AF (Bank Key)
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  'Rows with BLANK Vendor Key are ignored (left BLANK)
  '
  'Pass2:
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  'Duplicate Vendor Names and Account Groups indicate FAIL

These are the rules I used for Macro2:

  'This puts 'OK' or 'FAILED in Column 'BG' based on the following:
  '
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'If there is a duplicate Vendor Name and the Account Group is either either ZM05 or ZM014,
  '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, otherwise OK

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:

Option Explicit

Private Const nFirstDataROW = 2
Private Const sAutoFilterRANGE = "A1:BG1"

Sub MacroForColumnBF()
  'This puts 'OK' or 'FAILED in Column 'BF' based on the following:
  '
  'Pass 1:
  'Examine the contents of:
  'Column C  (Vendor Key)
  'Column AG (Acct No)
  'Column AF (Bank Key)
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  'Rows with BLANK Vendor Key are ignored (left BLANK)
  '
  'Pass2:
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  'Duplicate Vendor Names and Account Groups indicate FAIL

  Dim i As Long
  Dim iRow As Long
  Dim iRowCountForThisVendor As Long
  Dim iLastRow As Long
  
  Dim bHaveFailure As Boolean

  Dim sAcccountKeyFromColumnD As String
  Dim sAcccountKeyPrevious As String
  Dim sAccountNumberFromColumnAG As String
  Dim sPreviousConcatenation As String
  Dim sBankKeyFromColumnAF As String
  Dim sConcatenation As String
  Dim sPassFailValue As String
  Dim sRange As String
  Dim sSortRange As String
  Dim sVendorKeyFromColumnC As String
  Dim sVendorKeyPrevious As String
  Dim sVendorNameFromColumnH As String
  Dim sVendorNamePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BF'
  sRange = "BF2:BF" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Create the range to be sorted e.g. 'A1:BG1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
    
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Pass 1
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Sort by Column C (Vendor Key) - Primary Key
  'Sort by Column AF(Bank Key)   - Secondary Key
  'Sort by Column AG (Acct No)   - Tertiary Key
  Range(sSortRange).Sort _
        Key1:=Range("C1"), Order1:=xlAscending, _
        Key2:=Range("AF1"), Order2:=xlAscending, _
        Key3:=Range("AG1"), Order3:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers, _
        DataOption2:=xlSortTextAsNumbers, _
        DataOption3:=xlSortTextAsNumbers
        
        
  sVendorKeyPrevious = ""

  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
    sVendorKeyFromColumnC = Trim(Cells(iRow, "C"))
    
    If sVendorKeyFromColumnC <> sVendorKeyPrevious Then
      'Output Results for the previous Vendor if the old Vendor is different from the current vendor
      If bHaveFailure = True Then
        sPassFailValue = "FAILED"
      Else
        sPassFailValue = "OK"
      End If
    
      'Put the same Pass/Fail value in all rows for a specific Vendor Key
      For i = 1 To iRowCountForThisVendor
        Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
      Next i
      
      'Prepare for the NEXT Vendor Key
      bHaveFailure = False
      iRowCountForThisVendor = 0
    End If
    
    'Process only if the Vendor Key is NOT BLANK
    If Len(sVendorKeyFromColumnC) > 0 Then
    
      'Increment the Count for this Vendor Key
      iRowCountForThisVendor = iRowCountForThisVendor + 1
     
      'Get the values in 'AF' and 'AG'
      sBankKeyFromColumnAF = Trim(Cells(iRow, "AF"))
      sAccountNumberFromColumnAG = Trim(Cells(iRow, "AG"))
      
      'Get the concatenated value of'AF' and 'AG' for this row
      sConcatenation = sBankKeyFromColumnAF & sAccountNumberFromColumnAG
      
      'Set the failure flag if 'AF' and 'AG' are the same as the 'Previous values' of 'AF' and 'AG'
      If iRowCountForThisVendor > 1 Then
        If sConcatenation = sPreviousConcatenation Then
          bHaveFailure = True
        End If
      End If
     
      'Save the current value as the Previous value
      sPreviousConcatenation = sConcatenation
    
    End If
    
    'Save the current value for comparison purposes later
    sVendorKeyPrevious = sVendorKeyFromColumnC
    
  Next iRow
  
  
  'Output Results for the LAST Vendor Key
  If bHaveFailure = True Then
    sPassFailValue = "FAILED"
  Else
    sPassFailValue = "OK"
  End If
    
  'Put the same Pass/Fail value in all rows for a specific Vendor Key
  'if the Vendor Key is NOT BLANK (Adjust the row number because 'for loop' increments past the last row)
  iRow = iRow - 1
  For i = 1 To iRowCountForThisVendor
    Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
  Next i
  

  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Pass 2
  ''''''''''''''''''''''''''''''''''''''''''''''''

  'Sort by Column H (Vendor Name)   - Primary Key
  'Sort by Column D (Account Group) - Secondary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("D1"), Order2:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
  
  'Initialize variables
  sVendorNamePrevious = ""
  sAcccountKeyPrevious = ""
  
  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
  
    'Get the input data
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    sAcccountKeyFromColumnD = Trim(Cells(iRow, "D"))
    
    'Output 'FAILED' for this row and the previous row if the values match
    'otherwise output 'OK', unless the previous value was 'FAILED' (then do nothing)
    If sVendorNameFromColumnH = sVendorNamePrevious And sAcccountKeyFromColumnD = sAcccountKeyPrevious Then
        
      Cells(iRow, "BF") = "FAILED"
      Cells(iRow, "BF").Offset(-1, 0) = "FAILED"
        
    Else
                
      sPassFailValue = Cells(iRow, "BF").Offset(-1, 0)
      If sPassFailValue <> "FAILED" Then
        Cells(iRow, "BF").Offset(-1, 0) = "OK"
      End If
      
    End If
    
    'Save the current values as the Previous values
    If iRow <> iLastRow Then
      sVendorNamePrevious = sVendorNameFromColumnH
      sAcccountKeyPrevious = sAcccountKeyFromColumnD
    End If
    
  Next iRow
  
  
 'Output Results for the LAST Vendor Vendor Name
 'Row Number is ONE ROW after the last row (because 'for loop' increments past the last row)
  If sVendorNameFromColumnH = sVendorNamePrevious And sAcccountKeyFromColumnD = sAcccountKeyPrevious Then
        
    Cells(iRow, "BF").Offset(-1, 0) = "FAILED"
        
  Else
                
    sPassFailValue = Cells(iRow, "BF").Offset(-1, 0)
    If sPassFailValue <> "FAILED" Then
      Cells(iRow, "BF").Offset(-1, 0) = "OK"
    End If
  
  End If
  
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True


End Sub


Sub MacroForColumnBG()
  'This puts 'OK' or 'FAILED in Column 'BG' based on the following:
  '
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'If there is a duplicate Vendor Name and the Account Group is either either ZM05 or ZM014,
  '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, otherwise OK

  Dim i As Long
  Dim iRow As Long
  Dim iRowCountForThisVendor As Long
  Dim iLastRow As Long
  
  Dim bHaveFailure As Boolean

  Dim sAcccountKeyFromColumnD As String
  Dim sAcccountKeyPrevious As String
  Dim sCountryCodeFromColumnP As String
  Dim sCountryCodeForZM01 As String
  Dim sPassFailValue As String
  Dim sRange As String
  Dim sSortRange As String
  Dim sVendorNameFromColumnH As String
  Dim sVendorNamePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BG'
  sRange = "BG2:BG" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Create the range to be sorted e.g. 'A1:BG1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
    
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Calculate Results
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Sort by Column H (Vendor Name)   - Primary Key
  'Sort by Column D (Account Group) - Secondary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("D1"), Order2:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
  
  'Initialize variables
  sVendorNamePrevious = ""
  sAcccountKeyPrevious = ""
  iRowCountForThisVendor = 0
  
  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
  
    'Get the input data
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    sAcccountKeyFromColumnD = Trim(Cells(iRow, "D"))
    
        
   'Output Results for the previous Vendor if the old Vendor is different from the current vendor
    If sVendorNameFromColumnH <> sVendorNamePrevious Then
      If bHaveFailure = True Then
        sPassFailValue = "FAILED"
      Else
        sPassFailValue = "OK"
      End If
    
      'Put the same Pass/Fail value in all rows for a specific Vendor Key
      For i = 1 To iRowCountForThisVendor
        Cells(iRow, "BG").Offset(-i, 0) = sPassFailValue
      Next i
      
      'Prepare for the NEXT Vendor Key
      bHaveFailure = False
      iRowCountForThisVendor = 0
      sCountryCodeForZM01 = ""
    End If
    
    
    'Increment the Count for this Vendor Key
    iRowCountForThisVendor = iRowCountForThisVendor + 1
        
    'Get the 'Country Code'
    sCountryCodeFromColumnP = Trim(Cells(iRow, "P"))
    
    'Make the Pass/Fail Test
    If sCountryCodeForZM01 <> "" Then
      If sAcccountKeyFromColumnD = "ZM05" Or sAcccountKeyFromColumnD = "ZM14" Then
        If sCountryCodeFromColumnP <> sCountryCodeForZM01 Then
          bHaveFailure = True
        End If
      End If
    End If
    
    If sCountryCodeForZM01 = "" And sAcccountKeyFromColumnD = "ZM01" Then
      sCountryCodeForZM01 = sCountryCodeFromColumnP
    End If
     
    'Save the current values as the Previous value
    sVendorNamePrevious = sVendorNameFromColumnH
    
  Next iRow
  
  
  'Output results for the items associated with the last row
  'NOTE" Row number is one greater than the last row because 'for loop' increments past the last row
  If bHaveFailure = True Then
    sPassFailValue = "FAILED"
  Else
    sPassFailValue = "OK"
  End If
    
  'Put the same Pass/Fail value in all rows for a specific Vendor Key
  For i = 1 To iRowCountForThisVendor
    Cells(iRow, "BG").Offset(-i, 0) = sPassFailValue
  Next i
 
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True

End Sub

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:

Option Explicit

Private Const nFirstDataROW = 2
Private Const sAutoFilterRANGE = "A1:BG1"

Sub MacroForColumnBF()
  'This puts 'OK' or 'FAILED in Column 'BF' based on the following:
  '
  'Pass 1:
  'Examine the contents of:
  'Column H  (Vendor Name)
  'Column AG (Acct No)
  'Column AF (Bank Key)
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  '
  '
  'Pass2:
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'Duplicate Vendor Names and Account Groups indicate FAIL

  Dim i As Long
  Dim iRow As Long
  Dim iRowCountForThisVendor As Long
  Dim iLastRow As Long
  
  Dim bHaveFailure As Boolean

  Dim sAcccountKeyFromColumnD As String
  Dim sAcccountKeyPrevious As String
  Dim sAccountNumberFromColumnAG As String
  Dim sPreviousConcatenation As String
  Dim sBankKeyFromColumnAF As String
  Dim sConcatenation As String
  Dim sPassFailValue As String
  Dim sRange As String
  Dim sSortRange As String
  Dim sVendorNameFromColumnH As String
  Dim sVendorNamePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BF'
  sRange = "BF2:BF" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Create the range to be sorted e.g. 'A1:BG1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
    
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Pass 1
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Sort by Column H (Vendor Name) - Primary Key
  'Sort by Column AF(Bank Key)   - Secondary Key
  'Sort by Column AG (Acct No)   - Tertiary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("AF1"), Order2:=xlAscending, _
        Key3:=Range("AG1"), Order3:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers, _
        DataOption2:=xlSortTextAsNumbers, _
        DataOption3:=xlSortTextAsNumbers
        
        
  sVendorNamePrevious = ""

  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    
    If sVendorNameFromColumnH <> sVendorNamePrevious Then
      'Output Results for the previous Vendor if the old Vendor is different from the current vendor
      If bHaveFailure = True Then
        sPassFailValue = "FAILED"
      Else
        sPassFailValue = "OK"
      End If
    
      'Put the same Pass/Fail value in all rows for a specific Vendor Name
      For i = 1 To iRowCountForThisVendor
        Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
      Next i
      
      'Prepare for the NEXT Vendor Name
      bHaveFailure = False
      iRowCountForThisVendor = 0
    End If
    
    'Process only if the Vendor Name is NOT BLANK
    If Len(sVendorNameFromColumnH) > 0 Then
    
      'Increment the Count for this Vendor
      iRowCountForThisVendor = iRowCountForThisVendor + 1
     
      'Get the values in 'AF' and 'AG'
      sBankKeyFromColumnAF = Trim(Cells(iRow, "AF"))
      sAccountNumberFromColumnAG = Trim(Cells(iRow, "AG"))
      
      'Get the concatenated value of'AF' and 'AG' for this row
      sConcatenation = sBankKeyFromColumnAF & sAccountNumberFromColumnAG
      
      'Set the failure flag if 'AF' and 'AG' are the same as the 'Previous values' of 'AF' and 'AG'
      If iRowCountForThisVendor > 1 Then
        If sConcatenation = sPreviousConcatenation Then
          bHaveFailure = True
        End If
      End If
     
      'Save the current value as the Previous value
      sPreviousConcatenation = sConcatenation
    
    End If
    
    'Save the current value for comparison purposes later
    sVendorNamePrevious = sVendorNameFromColumnH
    
  Next iRow
  
  
  'Output Results for the LAST Vendor Key
  If bHaveFailure = True Then
    sPassFailValue = "FAILED"
  Else
    sPassFailValue = "OK"
  End If
    
  'Put the same Pass/Fail value in all rows for a specific Vendor Name
  'if the Vendor Name is NOT BLANK (Adjust the row number because 'for loop' increments past the last row)
  iRow = iRow - 1
  For i = 1 To iRowCountForThisVendor
    Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
  Next i
  

  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Pass 2
  ''''''''''''''''''''''''''''''''''''''''''''''''

  'Sort by Column H (Vendor Name)   - Primary Key
  'Sort by Column D (Account Group) - Secondary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("D1"), Order2:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
  
  'Initialize variables
  sVendorNamePrevious = Trim(Cells(nFirstDataROW, "H"))
  sAcccountKeyPrevious = Trim(Cells(nFirstDataROW, "D"))
  
  'Loop Through the data
  For iRow = nFirstDataROW + 1 To iLastRow
  
    'Get the input data
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    sAcccountKeyFromColumnD = Trim(Cells(iRow, "D"))
    
    'Output 'FAILED' for this row and the previous row if the values match
    'otherwise output 'OK', unless the previous value was 'FAILED' (then do nothing)
    If sVendorNameFromColumnH = sVendorNamePrevious And sAcccountKeyFromColumnD = sAcccountKeyPrevious Then
        
      Cells(iRow, "BF") = "FAILED"
      Cells(iRow, "BF").Offset(-1, 0) = "FAILED"
        
    Else
                
      sPassFailValue = Cells(iRow, "BF").Offset(-1, 0)
      If sPassFailValue <> "FAILED" Then
        Cells(iRow, "BF").Offset(-1, 0) = "OK"
      End If
      
    End If
    
    'Save the current values as the Previous values
    If iRow <> iLastRow Then
      sVendorNamePrevious = sVendorNameFromColumnH
      sAcccountKeyPrevious = sAcccountKeyFromColumnD
    End If
    
  Next iRow
  
  
 'Output Results for the LAST Vendor Vendor Name
 'Row Number is ONE ROW after the last row (because 'for loop' increments past the last row)
  If sVendorNameFromColumnH = sVendorNamePrevious And sAcccountKeyFromColumnD = sAcccountKeyPrevious Then
        
    Cells(iRow, "BF").Offset(-1, 0) = "FAILED"
        
  Else
                
    sPassFailValue = Cells(iRow, "BF").Offset(-1, 0)
    If sPassFailValue <> "FAILED" Then
      Cells(iRow, "BF").Offset(-1, 0) = "OK"
    End If
  
  End If
  
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True


End Sub


Sub MacroForColumnBG()
  'This puts 'OK' or 'FAILED in Column 'BG' based on the following:
  '
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'If there is a duplicate Vendor Name and the Account Group is either either ZM05 or ZM014,
  'then the country (column P) associated to it should be the same as the the country (column P) associated to ZM01.
  'There can be multiple 'ZM01' codes - to PASS a line must match one of them.
  'If fail, then mark all the lines failed, otherwise OK

  Dim i As Long
  Dim iCountryCodesForZM01 As Long
  Dim iRow As Long
  Dim iRowCountForThisVendor As Long
  Dim iLastRow As Long
  
  Dim bHaveCountryCodeMatch As Boolean
  Dim bHaveFailure As Boolean

  Dim sAcccountKeyFromColumnD As String
  Dim sAcccountKeyPrevious As String
  Dim sCountryCodeFromColumnP As String
  Dim sCountryCodeForZM01() As String
  Dim sPassFailValue As String
  Dim sRange As String
  Dim sSortRange As String
  Dim sVendorNameFromColumnH As String
  Dim sVendorNamePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BG'
  sRange = "BG2:BG" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Create the range to be sorted e.g. 'A1:BG1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
    
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Calculate Results
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Sort by Column H (Vendor Name)   - Primary Key
  'Sort by Column D (Account Group) - Secondary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("D1"), Order2:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
  
  'Initialize variables
  sVendorNamePrevious = ""
  sAcccountKeyPrevious = ""
  iRowCountForThisVendor = 0
  iCountryCodesForZM01 = 0
  ReDim sCountryCodeForZM01(1 To 1)
  
  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
  
    'Get the input data
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    sAcccountKeyFromColumnD = Trim(Cells(iRow, "D"))
    
        
   'Output Results for the previous Vendor if the old Vendor is different from the current vendor
    If sVendorNameFromColumnH <> sVendorNamePrevious Then
      If bHaveFailure = True Then
        sPassFailValue = "FAILED"
      Else
        sPassFailValue = "OK"
      End If
    
      'Put the same Pass/Fail value in all rows for a specific Vendor Key
      For i = 1 To iRowCountForThisVendor
        Cells(iRow, "BG").Offset(-i, 0) = sPassFailValue
      Next i
      
      'Prepare for the NEXT Vendor Key
      bHaveFailure = False
      iRowCountForThisVendor = 0
      iCountryCodesForZM01 = 0
      ReDim sCountryCodeForZM01(1 To 1)
    End If
    
    
    'Increment the Count for this Vendor Key
    iRowCountForThisVendor = iRowCountForThisVendor + 1
        
    'Get the 'Country Code'
    sCountryCodeFromColumnP = Trim(Cells(iRow, "P"))
    
    'Make the Pass/Fail Test
    If iCountryCodesForZM01 > 0 Then
      If sAcccountKeyFromColumnD = "ZM05" Or sAcccountKeyFromColumnD = "ZM14" Then
      
        'Check the country code against all 'ZM01' country codes
        bHaveCountryCodeMatch = False
        For i = 1 To iCountryCodesForZM01
          If sCountryCodeFromColumnP = sCountryCodeForZM01(i) Then
            bHaveCountryCodeMatch = True
            Exit For
          End If
        Next i
        bHaveFailure = Not bHaveCountryCodeMatch
        
      End If
    End If
    
    'If the country code is 'ZM01' add the country code to the list for this vendor
    If sAcccountKeyFromColumnD = "ZM01" Then
        iCountryCodesForZM01 = iCountryCodesForZM01 + 1
        ReDim Preserve sCountryCodeForZM01(1 To iCountryCodesForZM01)
        sCountryCodeForZM01(iCountryCodesForZM01) = sCountryCodeFromColumnP
    End If
         
    'Save the current values as the Previous value
    sVendorNamePrevious = sVendorNameFromColumnH
    
  Next iRow
  
  
  'Output results for the items associated with the last row
  'NOTE" Row number is one greater than the last row because 'for loop' increments past the last row
  If bHaveFailure = True Then
    sPassFailValue = "FAILED"
  Else
    sPassFailValue = "OK"
  End If
    
  'Put the same Pass/Fail value in all rows for a specific Vendor Key
  For i = 1 To iRowCountForThisVendor
    Cells(iRow, "BG").Offset(-i, 0) = sPassFailValue
  Next i
 
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True

End Sub

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

Dear Lewis,


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

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'.

  'This puts 'OK' or 'FAILED in Column 'BF' based on the following:
  '
  'Pass 1:
  'Examine the contents of:
  'Column H  (Vendor Name)
  'Column AG (Acct No)
  'Column AF (Bank Key)
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  '
  '
  'Pass2:
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'Duplicate Vendor Names and Account Groups indicate FAIL

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.

'This puts 'OK' or 'FAILED in Column 'BF' based on the following:
  '
  'Pass 1:
  'Examine the contents of:
  'Column H  (Vendor Name)
  'Column AG (Acct No)
  'Column AF (Bank Key)
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  '
  '
  'Pass2:
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'Duplicate Vendor Names and Account Groups indicate FAIL

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.

Responses to your image file comments:
a. Lines 2-3 Fail Pass 2 because column H and Column D are the same for both.
b. Lines 4-6 'OK' Now 
c. Lines 7-9 Line 7 = 'OK' (BLANKS in AF and AG)
             Lines 8-9 = 'FAILED'  - 'OK' Pass 1 , 'FAILED' Pass 2 because column H and Column D are the same for both.
d. Lines 11-12 originally correct.  They differ from rows 5-6 because 'D' in 5 and 6 were both 'ZM05' causing 'FAILED'.
             In rows 11-12, 'D' is 'ZM01' in 11, and 'ZM02' in 12.
e. Lines 14-15 originally correct.  Same answer as lines 11-12.
f. Lines 16-18	'OK' now.

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

Option Explicit

Private Const nFirstDataROW = 2
Private Const sAutoFilterRANGE = "A1:BG1"

Sub MacroForColumnBF()
  'This puts 'OK' or 'FAILED in Column 'BF' based on the following:
  '
  'Pass 1:
  'Examine the contents of:
  'Column H  (Vendor Name)
  'Column AG (Acct No)
  'Column AF (Bank Key)
  '
  'NOTE: Any rows with 'AF' and 'AG' both BLANK, are automatically 'OK'
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  '
  '
  '
  'Pass2:
  'Examine the contents of:
  'Column H (Vendor Name)
  'Column D (Account Group)
  '
  'NOTE: Any rows with 'AF' and 'AG' both BLANK, are automatically 'OK'
  'Duplicate Vendor Names and Account Groups indicate FAIL

  Dim i As Long
  Dim iRow As Long
  Dim iRowCountForThisVendor As Long
  Dim iLastRow As Long
  
  Dim bHaveFailure As Boolean

  Dim sAcccountKeyFromColumnD As String
  Dim sAcccountKeyPrevious As String
  Dim sAccountNumberFromColumnAG As String
  Dim sPreviousConcatenation As String
  Dim sBankKeyFromColumnAF As String
  Dim sConcatenation As String
  Dim sPassFailValue As String
  Dim sRange As String
  Dim sSortRange As String
  Dim sVendorNameFromColumnH As String
  Dim sVendorNamePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BF'
  sRange = "BF2:BF" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Create the range to be sorted e.g. 'A1:BG1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
    
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Pass 1
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Sort by Column H (Vendor Name) - Primary Key
  'Sort by Column AF(Bank Key)   - Secondary Key
  'Sort by Column AG (Acct No)   - Tertiary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("AF1"), Order2:=xlAscending, _
        Key3:=Range("AG1"), Order3:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers, _
        DataOption2:=xlSortTextAsNumbers, _
        DataOption3:=xlSortTextAsNumbers
        
        
  sVendorNamePrevious = ""

  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    
    If sVendorNameFromColumnH <> sVendorNamePrevious Then
      'Output Results for the previous Vendor if the old Vendor is different from the current vendor
      If bHaveFailure = True Then
        sPassFailValue = "FAILED"
      Else
        sPassFailValue = "OK"
      End If
    
      'Put the same Pass/Fail value in all rows for a specific Vendor Name
      For i = 1 To iRowCountForThisVendor
        Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
      Next i
      
      'Prepare for the NEXT Vendor Name
      bHaveFailure = False
      iRowCountForThisVendor = 0
    End If
    
    'Process only if the Vendor Name is NOT BLANK
    If Len(sVendorNameFromColumnH) > 0 Then
    
      'Increment the Count for this Vendor
      iRowCountForThisVendor = iRowCountForThisVendor + 1
     
      'Get the values in 'AF' and 'AG'
      sBankKeyFromColumnAF = Trim(Cells(iRow, "AF"))
      sAccountNumberFromColumnAG = Trim(Cells(iRow, "AG"))
      
      'Get the concatenated value of'AF' and 'AG' for this row
      sConcatenation = sBankKeyFromColumnAF & sAccountNumberFromColumnAG
      
      'Set the failure flag if 'AF' and 'AG' are the same as the 'Previous values' of 'AF' and 'AG'
      If Len(sConcatenation) > 0 Then
        If iRowCountForThisVendor > 1 Then
          If sConcatenation = sPreviousConcatenation Then
            bHaveFailure = True
          End If
        End If
      End If
     
      'Save the current value as the Previous value
      sPreviousConcatenation = sConcatenation
    
    End If
    
    'Save the current value for comparison purposes later
    sVendorNamePrevious = sVendorNameFromColumnH
    
  Next iRow
  
  
  'Output Results for the LAST Vendor Key
  If bHaveFailure = True Then
    sPassFailValue = "FAILED"
  Else
    sPassFailValue = "OK"
  End If
    
  'Put the same Pass/Fail value in all rows for a specific Vendor Name
  'if the Vendor Name is NOT BLANK (Adjust the row number because 'for loop' increments past the last row)
  'iRow does not need to be adjusted - it already has been incremented beyond the last row
  For i = 1 To iRowCountForThisVendor
    Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
  Next i
  

  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Pass 2
  ''''''''''''''''''''''''''''''''''''''''''''''''

  'Sort by Column H (Vendor Name)   - Primary Key
  'Sort by Column D (Account Group) - Secondary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("D1"), Order2:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
  
  'Initialize variables
  sVendorNamePrevious = Trim(Cells(nFirstDataROW, "H"))
  sAcccountKeyPrevious = Trim(Cells(nFirstDataROW, "D"))
  
  'Loop Through the data
  For iRow = nFirstDataROW + 1 To iLastRow
  
    'Get the input data
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    sAcccountKeyFromColumnD = Trim(Cells(iRow, "D"))
    
    'Get the values in 'AF' and 'AG'
    sBankKeyFromColumnAF = Trim(Cells(iRow, "AF"))
    sAccountNumberFromColumnAG = Trim(Cells(iRow, "AG"))
    
    'Get the concatenated value of'AF' and 'AG' for this row
    sConcatenation = sBankKeyFromColumnAF & sAccountNumberFromColumnAG
      
    
    'Output 'FAILED' for this row and the previous row if the values match
    'otherwise output 'OK', unless the previous value was 'FAILED' (then do nothing)
    If Len(sConcatenation) > 0 Then
      If sVendorNameFromColumnH = sVendorNamePrevious And sAcccountKeyFromColumnD = sAcccountKeyPrevious Then
        
        Cells(iRow, "BF") = "FAILED"
        Cells(iRow, "BF").Offset(-1, 0) = "FAILED"
        
      Else
                
        sPassFailValue = Cells(iRow, "BF").Offset(-1, 0)
        If sPassFailValue <> "FAILED" Then
          Cells(iRow, "BF").Offset(-1, 0) = "OK"
        End If
      
      End If
    End If
    
    'Save the current values as the Previous values
    If iRow <> iLastRow Then
      sVendorNamePrevious = sVendorNameFromColumnH
      sAcccountKeyPrevious = sAcccountKeyFromColumnD
    End If
    
  Next iRow
  
  
 'Output Results for the LAST Vendor Vendor Name
 'Row Number is ONE ROW after the last row (because 'for loop' increments past the last row)
  If sVendorNameFromColumnH = sVendorNamePrevious And sAcccountKeyFromColumnD = sAcccountKeyPrevious Then
        
    Cells(iRow, "BF").Offset(-1, 0) = "FAILED"
        
  Else
                
    sPassFailValue = Cells(iRow, "BF").Offset(-1, 0)
    If sPassFailValue <> "FAILED" Then
      Cells(iRow, "BF").Offset(-1, 0) = "OK"
    End If
  
  End If
  
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True


End Sub

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).

a. Lines 2-3 Fail Pass 2 because column H and Column D are the same for both.
 For check BF, we don’t care about column D. the cycle needs to go through the same vendor names (column H), and check the corresponding bank details (AF,AG)
 

b. Lines 4-6 'OK' Now 
 

c. Lines 7-9 Line 7 = 'OK' (BLANKS in AF and AG)
 

             Lines 8-9 = 'FAILED'  - 'OK' Pass 1 , 'FAILED' Pass 2 because column H and Column D are the same for both.
 
Same here. We don’t need to examine the column D

d. Lines 11-12 originally correct.  They differ from rows 5-6 because 'D' in 5 and 6 were both 'ZM05' causing 'FAILED'.
 

             In rows 11-12, 'D' is 'ZM01' in 11, and 'ZM02' in 12.
 

e. Lines 14-15 originally correct.  Same answer as lines 11-12.
 

f. Lines 16-18 'OK' now.

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.

Option Explicit

Private Const nFirstDataROW = 2
Private Const sAutoFilterRANGE = "A1:BZ1"     

Sub MacroForColumnBF()

Sub MacroForColumnBH()
  'This puts 'OK' or 'FAILED in Column 'BH' based on the following:
  '
  ' 'FAILED' if a duplicate Column C  (Vendor Number),
  '                                D  (Account Group),
  '                                H  (Vendor Name),
  '                                AF (Bank Key),
  '                                AG (Acct No)
  ' 'OK' otherwise

  Dim iRow As Long
  Dim iLastRow As Long
  
  Dim sPassFailValue As String
  Dim sPassFailValuePrevious As String
  Dim sRange As String
  Dim sSortRange As String
  Dim s1 As String
  Dim s2 As String
  Dim s3 As String
  Dim s4 As String
  Dim s5 As String
  Dim sValue As String
  Dim sValuePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BF'
  sRange = "BH2:BH" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Concatenate columns C D H AF AG
  'Put the results in Column 'BH'
  For iRow = nFirstDataROW To iLastRow
    s1 = Cells(iRow, "C")
    s2 = Cells(iRow, "D")
    s3 = Cells(iRow, "H")
    s4 = Cells(iRow, "AF")
    s5 = Cells(iRow, "AG")
    
    Cells(iRow, "BH") = s1 & "-" & s2 & "-" & s3 & "-" & s4 & "-" & s5
  Next iRow
  
  'Create the range to be sorted e.g. 'A1:BZ1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
      
  'Sort by Column BH
  Range(sSortRange).Sort _
        Key1:=Range("BH1"), Order1:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
        
        
  'Seed the Previous Value with the value from the first row
  sValuePrevious = Cells(nFirstDataROW, "BH")
        
  'Loop Through the data
  For iRow = nFirstDataROW + 1 To iLastRow
    sValue = Cells(iRow, "BH")
    
    If sValue <> sValuePrevious Then
      sPassFailValue = "OK"
    Else
      sPassFailValue = "FAILED"
    End If
    
    'Put the Pass/Fail value in the 'Previous Row' (only if it doesn't contain 'FAILED' already)
    sPassFailValuePrevious = Cells(iRow - 1, "BH")
    If sPassFailValuePrevious <> "FAILED" Then
      Cells(iRow - 1, "BH") = sPassFailValue
    End If
    
    'Put FAILED value in the current row if it is failed
    If sPassFailValue = "FAILED" Then
      Cells(iRow, "BH") = sPassFailValue
    End If
        
    'Save the current value for comparison purposes later
    sValuePrevious = sValue
    
  Next iRow

  'Output Results for the LAST Row (same as the value for the next to last row)
  Cells(iLastRow, "BH") = sPassFailValue
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True


End Sub



Sub MacroForColumnBI()
  'This puts 'OK' or 'FAILED in Column 'BI' based on the following:
  '
  ''OK' if no leading or trailing spaces in any cell
  'otherwise 'FAILED'
  '
  'The Columns checked are column 'A' (=1) to column 'AN' (= 40)

  Dim iColumn As Long
  Dim iRow As Long
  Dim iLastRow As Long
  
  Dim sPassFailValue As String
  Dim sRange As String
  Dim sValue As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BI'
  sRange = "BI2:BI" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Calculate Results
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  
  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
  
    sPassFailValue = "OK"
  
    For iColumn = 1 To 40
      sValue = ActiveSheet.Cells(iRow, iColumn)
      If Len(sValue) <> Len(Trim(sValue)) Then
        sPassFailValue = "FAILED"
        Exit For
      End If
    Next iColumn
   
   'Output Results for the row
    ActiveSheet.Cells(iRow, "BI") = sPassFailValue
   
  Next iRow
    
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True

End Sub

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:

Sub MacroForColumnBF()
  'This puts 'OK' or 'FAILED in Column 'BF' based on the following:
  '
  'Pass 1:
  'Examine the contents of:
  'Column H  (Vendor Name)
  'Column AF (Bank Key)
  'Column AG (Acct No)
  '
  'Any rows with 'AF' and 'AG' both BLANK, are 'OK', provided all other entries for the same vendor are unique.
  '
  '
  'If there is a duplicate in any row for a specific Vendor in columns 'AF' and 'AG'
  'ALL rows for that vendor FAIL
  
  Dim i As Long
  Dim iRow As Long
  Dim iRowCountForThisVendor As Long
  Dim iLastRow As Long
  
  Dim bHaveFailure As Boolean

  Dim sAcccountKeyFromColumnD As String
  Dim sAcccountKeyPrevious As String
  Dim sAccountNumberFromColumnAG As String
  Dim sPreviousConcatenation As String
  Dim sBankKeyFromColumnAF As String
  Dim sConcatenation As String
  Dim sPassFailValue As String
  Dim sRange As String
  Dim sSortRange As String
  Dim sVendorNameFromColumnH As String
  Dim sVendorNamePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BF'
  sRange = "BF2:BF" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Create the range to be sorted e.g. 'A1:BZ1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
    
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Pass 1
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Sort by Column H (Vendor Name) - Primary Key
  'Sort by Column AF(Bank Key)   - Secondary Key
  'Sort by Column AG (Acct No)   - Tertiary Key
  Range(sSortRange).Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("AF1"), Order2:=xlAscending, _
        Key3:=Range("AG1"), Order3:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers, _
        DataOption2:=xlSortTextAsNumbers, _
        DataOption3:=xlSortTextAsNumbers
        
        
  sVendorNamePrevious = ""

  'Loop Through the data
  For iRow = nFirstDataROW To iLastRow
    sVendorNameFromColumnH = Trim(Cells(iRow, "H"))
    
    If sVendorNameFromColumnH <> sVendorNamePrevious Then
      'Output Results for the previous Vendor if the old Vendor is different from the current vendor
      If bHaveFailure = True Then
        sPassFailValue = "FAILED"
      Else
        sPassFailValue = "OK"
      End If
    
      'Put the same Pass/Fail value in all rows for a specific Vendor Name
      For i = 1 To iRowCountForThisVendor
        Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
      Next i
      
      'Prepare for the NEXT Vendor Name
      bHaveFailure = False
      iRowCountForThisVendor = 0
    End If
    
    'Process only if the Vendor Name is NOT BLANK
    If Len(sVendorNameFromColumnH) > 0 Then
    
      'Increment the Count for this Vendor
      iRowCountForThisVendor = iRowCountForThisVendor + 1
     
      'Get the values in 'AF' and 'AG'
      sBankKeyFromColumnAF = Trim(Cells(iRow, "AF"))
      sAccountNumberFromColumnAG = Trim(Cells(iRow, "AG"))
      
      'Get the concatenated value of'AF' and 'AG' for this row
      sConcatenation = sBankKeyFromColumnAF & sAccountNumberFromColumnAG
      
      'Set the failure flag if 'AF' and 'AG' are the same as the 'Previous values' of 'AF' and 'AG'
      If Len(sConcatenation) > 0 Then
        If iRowCountForThisVendor > 1 Then
          If sConcatenation = sPreviousConcatenation Then
            bHaveFailure = True
          End If
        End If
      End If
     
      'Save the current value as the Previous value
      sPreviousConcatenation = sConcatenation
    
    End If
    
    'Save the current value for comparison purposes later
    sVendorNamePrevious = sVendorNameFromColumnH
    
  Next iRow
  
  
  'Output Results for the LAST Vendor Key
  If bHaveFailure = True Then
    sPassFailValue = "FAILED"
  Else
    sPassFailValue = "OK"
  End If
    
  'Put the same Pass/Fail value in all rows for a specific Vendor Name
  'if the Vendor Name is NOT BLANK (Adjust the row number because 'for loop' increments past the last row)
  'iRow does not need to be adjusted - it already has been incremented beyond the last row
  For i = 1 To iRowCountForThisVendor
    Cells(iRow, "BF").Offset(-i, 0) = sPassFailValue
  Next i
  

  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True


End Sub

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.

Sub MacroForColumnBH()
  'This puts 'OK' or 'FAILED in Column 'BH' based on the following:
  '
  ' 'FAILED' if a duplicate Column C  (Vendor Number),
  '                                D  (Account Group),
  '                                H  (Vendor Name),
  '                                AF (Bank Key),
  '                                AG (Acct No)
  ' 'OK' otherwise

  Dim iRow As Long
  Dim iLastRow As Long
  
  Dim sPassFailValue As String
  Dim sPassFailValuePrevious As String
  Dim sRange As String
  Dim sSortRange As String
  Dim s1 As String
  Dim s2 As String
  Dim s3 As String
  Dim s4 As String
  Dim s5 As String
  Dim sValue As String
  Dim sValuePrevious As String
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Initialization
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Make Sheet 'Load Data' the Active Sheet
  Sheets("Load Data").Select
  
  'Disable Events
  'Inhibit automatic calculation on the Active Sheet
  Application.EnableEvents = False
  ActiveSheet.EnableCalculation = False
  

  'Turn off AutoFilter
  ActiveSheet.AutoFilterMode = False
  
  'Get the Last Row
  iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  'Remove all data from the failure Column 'BF'
  sRange = "BH2:BH" & iLastRow
  ActiveSheet.Range(sRange).ClearContents
    
  'Concatenate columns C D H AF AG
  'Put the results in Column 'BH'
  For iRow = nFirstDataROW To iLastRow
    s1 = Cells(iRow, "C")
    s2 = Cells(iRow, "D")
    s3 = Cells(iRow, "H")
    s4 = Cells(iRow, "AF")
    s5 = Cells(iRow, "AG")
    
    Cells(iRow, "BH") = s1 & "-" & s2 & "-" & s3 & "-" & s4 & "-" & s5
  Next iRow
  
  'Create the range to be sorted e.g. 'A1:BZ1436'
  sSortRange = Left(sAutoFilterRANGE, Len(sAutoFilterRANGE) - 1) & iLastRow
      
  'Sort by Column BH
  Range(sSortRange).Sort _
        Key1:=Range("BH1"), Order1:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
        
        
  'Seed the Previous Value with the value from the first row
  sValuePrevious = Cells(nFirstDataROW, "BH")
        
  'Loop Through the data
  For iRow = nFirstDataROW + 1 To iLastRow
    sValue = Cells(iRow, "BH")
    
    If sValue <> sValuePrevious Then
      sPassFailValue = "OK"
    Else
      sPassFailValue = "FAILED"
    End If
    
    'Put the Pass/Fail value in the 'Previous Row' (only if it doesn't contain 'FAILED' already)
    sPassFailValuePrevious = Cells(iRow - 1, "BH")
    If sPassFailValuePrevious <> "FAILED" Then
      Cells(iRow - 1, "BH") = sPassFailValue
    End If
    
    'Put FAILED value in the current row if it is failed
    If sPassFailValue = "FAILED" Then
      Cells(iRow, "BH") = sPassFailValue
    End If
        
    'Save the current value for comparison purposes later
    sValuePrevious = sValue
    
  Next iRow

  'Output Results for the LAST Row (same as the value for the next to last row)
  Cells(iLastRow, "BH") = sPassFailValue
  
  ''''''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  ''''''''''''''''''''''''''''''''''''''''''''''''
  
  'Turn On AutoFilter
  ActiveSheet.Range(sAutoFilterRANGE).AutoFilter
  
  'Enable Events
  'Enabled automatic calculation on the Active worksheet (and calculate now)
  Application.EnableEvents = True
  ActiveSheet.EnableCalculation = True


End Sub

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:

 ''''''''''''''''''''''''''
  '1st Pass:
  'Credit Check (Column  G) must be unique for 'ZM01', 'ZM07', 'ZM11' (Column D) for the same Vendor Name (Column H).
  'Bank Key     (Column AF) must be unique for 'ZM01', 'ZM07', 'ZM11' (Column D) for the same Vendor Name (Column H).
  'Acct No      (Column AG) must be unique for 'ZM01', 'ZM07', 'ZM11' (Column D) for the same Vendor Name (Column H).
  '
  'NOTE: Single Line Credit Check (Column G), VAT number (column U), Tax Number 2(Column V) are not checked here (are checked later).
  '
  ''''''''''''''''''''''''''
  ''''''''''''''''''''''''''
  '2nd Pass:
  'Credit Check (Column  G) is required
  'If Country Code (Column P) is 'US', then Tax number 2 (Column V) is REQUIRED.
  'If Country Code (Column P) is 'GB', then VAT number   (Column U) is REQUIRED.
  ''''''''''''''''''''''''''
  ''''''''''''''''''''''''''

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.