Hi Vabz,
i have done part of it and i need some amendments alone.
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”.
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 code i have used for.
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
Sub clearBF()
Columns("BF:BF").Select
Selection.ClearContents
End Sub
Sub clearBG()
Columns("BG:BG").Select
Selection.ClearContents
End Sub
Please help me in the final small endorsement.
Lax
On Saturday, November 15, 2014 8:52:26 PM UTC+5:30, Vabz wrote:
> Can you explain it bit more with one example & sample of 9 to 10 lines
> only...
>
>
> Cheers!!
>
> +++++
> *I did not do this for you. God is here working through me for you.*
>
> On Wed, Nov 12, 2014 at 4:52 PM, Laxmanan M <laxfly...@gmail.com
> <javascript:>> wrote:
>
>> Thanks vabz,
>>
>> How to achieve the second macro using vba.I tried here but am failing
>> when trying to fetch details..
>>
>> Lax
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros...@googlegroups.com <javascript:>.
>> To post to this group, send email to excel-...@googlegroups.com
>> <javascript:>.
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
