Handling duplicates can be very tasking, most especially when you have a large database to deal with.
Data duplication can set your budget spinning, introducing irregularities, inconsistencies, wastage of resources, and perhaps lack of trust.
However, there’s a way out. You can prevent duplicates before they happen by combining a simple function with data validation.
Microsoft Excel has built-in tools to expose and, even delete duplicates in a simple way.
Today, we’ll only focus on how to expose duplicates using Excel with the Excel Data Validation Feature + COUNTIF() function.
This feature checks values as you enter them and depending on the rules you specify, reject or accepts the value.
How it works
The COUNTIF() function uses the following SYNTAX, COUNTIF(range, condition) to count the number of cells in a range that meet a specific condition.
What you’ll do is supply the range and a condition as arguments using the syntax COUNTIF(range, condition)
Let’s use this function to count the number of times a phone no. occurs within a column.
The phone no. column of the sheet shown in Figure 1 allows duplicates
Right now, we’ll use data validation to prevent duplicate number in the phone no. column.
First, enter the following function cell D4
= COUNTIF(TABLE1[ phone no.],C4)
The function uses structured referencing because the data is formatted as a Table object. Because the value +1(716)532-2150 occurs only one time within the column, the function returns 1. However, if you repeat one of the values, the respective functions return 2 as shown in figure 2 below