Duplicate Entries Macro

Question

Hi,

i have 2 sheets, one of them is were transaction are stored, and the other is were postings of batch occurs. The problem is that i am trying to avoid duplicate entries of invoices.

The sheets name' s are:

 Invoices Records

 Batch postings

Sheet Invoices Records;

 A12:A has PI, D12:D has supplier names, F12:F has invoice numbers

Sheet Batch Postings

D3:D36 has PI, G3:G36 has supplier names, I3:I36 has invoice numbers

In batch postings is were i write transaction,s from than the transactions are than posted onto Invoices Records by push button called POST.

The macro i am trying to do is that when, for example i having an invoice already posted on sheet Invoices Records from the last batch postings, and i am posting a new batch invoices on Batch Postings sheet and by mistake i write the same invoices number within the same supplier and also it has to be a PI an input box appears and advice me that the same invoice number with the same supplier is already posted.

This also applies within the batch postings sheet to.

So the macro i think has to be on going, by target range.

Thanks

Answer 1

It should work that way now, PC Vodafone and PI Vodafone would not be considered duplicates.

Answer 2

OK - then I think I have what you need.  This code defines a duplicate as entries that match on all 3 fields, so the following are all different entries:

PI  Vodafone  125

PC Vodafone 125

PI Electronic 125

The code first checks the Batch Postings sheet for duplicates and gives you a message for each duplicate found telling you what the entries look like and provides the row numbers involved on that sheet.

After doing that, it compares entries on the Batch Postings sheet to existing entries on the Invoice Records sheet and does much the same: when a duplicate based on all 3 fields is identified, the content of the entry is reported along with the row number from the Batch Postings sheet where it was found.

Obviously you could do other things besides just toss up a message, such as highlighting the duplicate entries, and also you could halt further processing until this process runs without finding any duplicates at all (find one, fix it, run again, find another, fix it, run again ... repeat until no duplicates found).

This could even be turned into a Function instead of a Sub and then your existing posting code could call it before doing anything and if the results showed some duplicate entry - then don't do the regular posting actions.  If you need help with anything like that, let me know.

Here goes...

Sub CheckForDuplicates()

  'information regarding the Batch Postings sheet

  Const bpWSName = "Batch Postings"

  Const bpPICol = "D"

  Const bpNameCol = "G"

  Const bpInvCol = "I"

  Const bpFirstRow = 3

  Const bpLastRow = 36

  'variables for Batch Postings sheet

  Dim bpWS As Worksheet

  Dim bpPIList As Range

  Dim anyBPPIEntry As Range

  Dim bpNamesList As Range

  Dim bpAnyNameEntry As Range

  Dim bpInvList As Range

  Dim bpAnyInvEntry As Range

  'information regarding the Invoice Records sheet

  Const invWSName = "Invoice Records"

  Const invPICol = "A"

  Const invNameCol = "D"

  Const invInvCol = "F"

  Const invFirstRow = 12

  'variables for Invoice Records sheet

  Dim invWS As Worksheet

  Dim invPIList As Range

  Dim anyInvPIEntry As Range

  Dim invNamesList As Range

  Dim invAnyNameEntry As Range

  Dim invInvList As Range

  Dim invAnyInvEntry As Range

  Dim invLastRow As Long

  'general use variables

  Dim foundDuplicateEntry As Range

  Dim isDuplicate As Boolean

  Dim OLC As Integer

  Dim ILC As Integer

  Dim bpDuplicatesCount As Long

  Dim invDuplicatesCount As Long

  'data flow is FROM Batch Postings to Invoice Records sheet

  'set references to the Batch Postings sheet

  Set bpWS = ThisWorkbook.Worksheets(bpWSName)

  Set bpPIList = bpWS.Range(bpPICol & bpFirstRow & ":" & _

   bpPICol & bpLastRow)

  Set bpNamesList = bpWS.Range(bpNameCol & bpFirstRow & ":" & _

   bpNameCol & bpLastRow)

  Set bpInvList = bpWS.Range(bpInvCol & bpFirstRow & ":" & _

   bpInvCol & bpLastRow)

  'set references to the Batch Posting sheet

  Set invWS = ThisWorkbook.Worksheets(invWSName)

  invLastRow = invWS.Range(invInvCol & Rows.Count).End(xlUp).Row

  Set invPIList = bpWS.Range(invPICol & invFirstRow & ":" & _

   invPICol & invLastRow)

  Set invNamesList = invWS.Range(invNameCol & invFirstRow & ":" & _

   invNameCol & invLastRow)

  Set invInvList = invWS.Range(invInvCol & invFirstRow & ":" & _

   invInvCol & invLastRow)

  '

  'get down to work now

  'first see if there are any duplicate entries

  'on the Batch Posting sheet

  '

  For OLC = 1 To bpInvList.Cells.Count - 1

    isDuplicate = False

    If Not IsEmpty(bpInvList.Cells(OLC, 1)) Then

      For ILC = OLC + 1 To bpInvList.Cells.Count

        'the bpoice Number is least likely to be duplicated

        'so test them first

          If bpInvList.Cells(OLC, 1) = bpInvList.Cells(ILC, 1) Then

            'the bpoice Number is a duplicate

            'check the PI and Supplier Name

            If bpPIList.Cells(OLC, 1) = bpPIList.Cells(ILC, 1) _

             And bpNamesList.Cells(OLC, 1) = bpNamesList(ILC, 1) Then

              'a duplicate on the Batch Posting sheet

              isDuplicate = True

              bpDuplicatesCount = bpDuplicatesCount + 1

              Exit For ' exit the ILC loo;

            End If

          End If

      Next ' end Inner Loop Count

    End If

    If isDuplicate Then

      MsgBox bpPIList.Cells(OLC, 1) & " : " & bpNamesList.Cells(OLC, 1) _

       & " : " & bpInvList.Cells(OLC, 1) & vbCrLf _

       & "Has duplicate entry/entries on the " & bpWSName & " sheet." _

       & vbCrLf & "Rows are: " & bpInvList.Cells(OLC, 1).Row & " and " & bpInvList.Cells(ILC, 1).Row, _

       vbOKOnly + vbCritical, "DUPLICATE Entry Identified"

    End If

  Next ' end Outer Loop Count

  '

  'next test: are any of the entries on the Batch Postings

  'sheet already on the Invoice Records sheet?

  '

  For Each bpAnyInvEntry In bpInvList

    If Not IsEmpty(bpAnyInvEntry) Then

      Set foundDuplicateEntry = _

       invInvList.Find(What:=bpAnyInvEntry.Text, _

        LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

      If Not foundDuplicateEntry Is Nothing Then

        'matched on Invoice Number

        'check PI and Supplier Name

        If bpWS.Range(bpPICol & bpAnyInvEntry.Row) = invWS.Range(invPICol & foundDuplicateEntry.Row) _

         And bpWS.Range(bpInvCol & bpAnyInvEntry.Row) = invWS.Range(invInvCol & foundDuplicateEntry.Row) Then

          'absolute duplicate found

          invDuplicatesCount = invDuplicatesCount + 1

          MsgBox bpWS.Range(bpPICol & bpAnyInvEntry.Row) & " : " _

           & bpWS.Range(bpNameCol & bpAnyInvEntry.Row) _

           & " : " & bpAnyInvEntry & vbCrLf _

           & "Has duplicate entry/entries on the " & invWSName & " sheet." _

           & vbCrLf & "At Row: " & bpAnyInvEntry.Row & " on the " & bpWS.Name & " sheet.", _

           vbOKOnly + vbCritical, "DUPLICATE Entry Identified"

        End If

      End If

    End If

  Next ' end bpAnyInvEntry loop

'announce task completed

  If bpDuplicatesCount = 0 And invDuplicatesCount = 0 Then

    MsgBox "Comparisons completed.", vbOKOnly + vbInformation, "Task Accomplished - No Duplicates Found."

  Else

    If bpDuplicatesCount > 0 Then

      MsgBox "There are " & bpDuplicatesCount & " duplicate entries on the " & bpWS.Name & " worksheet.", _

       vbOKOnly + vbCritical, "Duplicate Entries Found"

    End If ' Corrected from original posting

    If invDuplicatesCount > 0 Then

      MsgBox "There are " & invDuplicatesCount & " entries on the " & bpWS.Name & " worksheet " _

       & "that already exist on the " & invWS.Name & " worksheet.", _

       vbOKOnly + vbCritical, "Existing Duplicate Entries Found"

    End If

  End If

'housekeeping cleanup

  Set bpWS = Nothing

  Set bpPIList = Nothing

  Set bpNamesList = Nothing

  Set bpInvList = Nothing

  Set invWS = Nothing

  Set invPIList = Nothing

  Set invNamesList = Nothing

  Set invInvList = Nothing

End Sub

Answer 3

What do you mean by function, instead of sub could this work for example everytime i records and entry

Answer 4

Sry for not providing you with reliable information on my question.

PI stands for purchases invoice this is a type that helps me identify between PC purchases credit and PD purchases discount,.

Invoices are only unique within the same supplier only, but when other supplier's are involved this same invoice number can be also found in other suppliers.

So the duplicate entries macro must be based on, PI that means purchases invoice any other type of example PC or PD must be ignored, supplier name and also invoice number.

For Example:

Type  Supplier Name   Invoice Number

PI        Vodafone             125

PI          Vodafone            146

PI          Electronic            125

There are 2 invoices that have the same supplier name vodafone but there can only be one unique number within that supplier. But also there can be another supplier with the same invoice number.

Sry again, will this provide's you enough information.

Thanks for the help.

Answer 5

Are the Invoice Numbers always unique regardless of what supplier (and/or PI) is associated with an Invoice Number?  Or is there even a remote possibility that 2 suppliers could be legitimately assigned the same Invoice Number?

This makes a big difference in how much work has to be done to identify duplicates - if the Invoice Numbers are always unique, then we only have to examine them, but if this situation is legitimate:

PI        Name         Invoice #

PI01    Sup A         PA-12345-B

PI01    Sup B         PA-12345-B

where 2 suppliers can be assigned same Invoice Number, then we have to compare all 3 entries.

You've mentioned a PI - what is that?  Does it ever need to be compared to see if two entries are duplicates?

Basic question, I guess is this:  What pieces of information must appear in entries for them to be considered duplicates?  Possible answers:

1. If Invoice Numbers are always supposed to be unique across all suppliers, then we only have to find duplicate invoice numbers,  or
2. If supplier name and invoice number are the same, then they are duplicates (but 2 or more suppliers could have same invoice number) - the PI doesn't matter at all.
3. The combination of PI + Supplier Name + Invoice Number must always be unique (but the combination of Supplier + Invoice Number might not have to be).