If you are serious about storing information in MS Excel, it is important that you set up your data correctly from day one. By preventing duplicate entries, it can save a lot of problems later.
Let's say you keep a list of customers, and you're worried that some information could be entered more than once. This can create problems if you use data to operate important parts of your company:
- Reliance on incomplete or incorrect information
- Financial management problems, such as sending duplicate invoices
- Your information may not be “database ready” when moving to a more complex platform.
This article focuses on how you can be warned about possible duplications during data entry.
Creating a unique key for your data
The problem of identifying duplicates is that not everyone sees the data in the same way. ABC Ltd can be recorded in many different ways, such as ABC Limited or ABC Ltd, which makes it difficult to find duplicate records.
One way is to create a data key — a unique combination of text and number created from the first three letters of the company name and street address. Thus, if data entry officers follow your business rules for registering new customers, any duplicates are more likely to be picked up.
Using VBA in Excel for Spot Duplicate Data Entries
Excel has several options for preventing duplication, including formulas and built-in formatters. But some simple VBA encodings are probably the most useful and flexible option because it allows you to fine-tune the solution for your own situation.
We will use the “change” event of the VBA worksheet to help decide whether to re-write. Here is the code - which should be placed in the code area for the corresponding sheet:
Private Sub workheet_change (ByVal Target As Range)
If Target.Column = 1 Then
prevData = "a1: a" & Target.Row - 1
newData = Range ("a" & Target.Row) .Text
If Application.WorksheetFunction.CountIf (Range (prevData), newData)> 0 Then
dupData = "Possible duplicate!"
yet
dupData = "Data is fine"
End If
MsgBox dupData
End If
End sub
Assuming your “key” data is in column 1, after adding a new record, the code checks whether the key has been previously added and, if so, warns the user about the possibility of duplication. This is the best option because it allows the data entry operator to decide whether to continue or not.
COUNTERS a function in Excel compares a range of cells with a specific text and returns the number of cells that are duplicates. In this case, we match the new key of the record, which is entered with all previous keys, and if the function returns a number greater than 0, then it must be duplicated.
Code improvements
It is one thing to suspect that a record is a duplicate, but you need to decide what to do with the information. The code can be adapted to highlight the previous identical entry to determine the next step; sometimes a duplicate entry has incorrect information, and it just needs to be fixed.
Having found duplicate data, you have seen how a small VBA code can improve the quality of your data, save you time and warn you of potential problems.
