If you're serious about storing information in MS Excel it's important you set up your data correctly from day one.
By preventing duplicate entries it can save a lot of problems later on.
Let's say you're maintaining a list of customers and you're concerned some information may have been entered more than once.
This could create problems if you're using the data to run important parts of your business:
Creating A Unique Key For Your Data The problem in identifying duplicates is that not everyone sees data in the same way.
ABC Ltd could be recorded in many different variations such as ABC Limited or A.
B.
C.
Ltd making it harder to find repeated entries.
One technique is to create a data key - a unique text/number combination created from the first three letters of the business name and the street address.
This way, if data entry staff are following your business rules for recording new customers, any duplications have a better chance of being picked up.
Using VBA In Excel To Spot Duplicate Data Entries Excel has several choices available to prevent duplicates including formulas and built-in formatting tools.
But some simple VBA coding is probably the most useful and flexible option as it enables you to fine tune a solution for your own situation.
We'll make use of the VBA worksheet "change" event to help decide whether or not a duplicate entry is being attempted.
Here's the code - which must be placed in the code area for the relevant worksheet:
This is the best option as it lets the data entry operator to make the decision as to whether to continue or not.
The countIf function in Excel compares a range of cells to some specified text and returns the number of cells that are duplicates.
In this case we're comparing the new record key being entered with all previous keys and if the function returns a number greater than 0 then it must be a duplicate entry.
Code Enhancements It's one thing to suspect that a record is a duplicate but you need to decide what to do with the information.
The code could be adapted to highlight the previous identical record to determine the next step; perhaps the duplicate record has incorrect information and it just needs to be corrected.
By finding duplicate data, you've seen how a small amount of VBA code can improve the quality of your data, save you time and alert you to potential problems.
By preventing duplicate entries it can save a lot of problems later on.
Let's say you're maintaining a list of customers and you're concerned some information may have been entered more than once.
This could create problems if you're using the data to run important parts of your business:
- Reliance on incomplete or incorrect information
- Financial management issues, for example sending duplicate invoices
- Your information may not be "database ready" when moving to a more sophisticated platform
Creating A Unique Key For Your Data The problem in identifying duplicates is that not everyone sees data in the same way.
ABC Ltd could be recorded in many different variations such as ABC Limited or A.
B.
C.
Ltd making it harder to find repeated entries.
One technique is to create a data key - a unique text/number combination created from the first three letters of the business name and the street address.
This way, if data entry staff are following your business rules for recording new customers, any duplications have a better chance of being picked up.
Using VBA In Excel To Spot Duplicate Data Entries Excel has several choices available to prevent duplicates including formulas and built-in formatting tools.
But some simple VBA coding is probably the most useful and flexible option as it enables you to fine tune a solution for your own situation.
We'll make use of the VBA worksheet "change" event to help decide whether or not a duplicate entry is being attempted.
Here's the code - which must be placed in the code area for the relevant worksheet:
Private Sub worksheet_change(ByVal Target As Range) If Target.Assuming your "key" data is in column 1, after adding a new record the code checks to see if the key has previously been added and if so, alerts the user about the possibility of a duplicate.
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!" Else dupData = "Data ok" End If MsgBox dupData End If End Sub
This is the best option as it lets the data entry operator to make the decision as to whether to continue or not.
The countIf function in Excel compares a range of cells to some specified text and returns the number of cells that are duplicates.
In this case we're comparing the new record key being entered with all previous keys and if the function returns a number greater than 0 then it must be a duplicate entry.
Code Enhancements It's one thing to suspect that a record is a duplicate but you need to decide what to do with the information.
The code could be adapted to highlight the previous identical record to determine the next step; perhaps the duplicate record has incorrect information and it just needs to be corrected.
By finding duplicate data, you've seen how a small amount of VBA code can improve the quality of your data, save you time and alert you to potential problems.