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.
ALSO READ: Microsoft Launches A New Tablet Called “Surface Go”
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)
For instance,
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
