Buzz
As we mentioned in the introduction, typically, to check for duplicate data, you may manually inspect and count directly on your Excel file. However, this method can be laborious and sometimes inaccurate. The best approach is to utilize the comparison functions available in Excel to compute and verify.
Comparing Functions in Excel
Utilizing Comparison Functions in Excel
Imagine you need to compare data in two columns as shown in the Excel spreadsheet below:
1. Using the Exact Function to Compare Data
Description
The Exact function compares two text strings and returns True if the two strings are identical, and False otherwise. Note that the Exact function is case-sensitive during comparison.
Syntax
EXACT(Text1, Text2)
Where: Text1, Text2 are the two text strings to be compared, which are mandatory parameters.
Note
The Exact function distinguishes between uppercase and lowercase letters during comparison but does not differentiate formats.
Consider Example 1:
Comparing corresponding data along the middle row of Column 1 and Column 2. In the cell where comparison is needed -> input the formula: =EXACT(B3,C3)
Press Enter the result returns False which means the two values are not the same due to differing initial lowercase and uppercase letters:
Similarly -> you copy the formula for the remaining values to get the result as shown below:
Note additionally that with this function, you cannot compare one value in column 1 against all the data in column 2 to find duplicates. This function only allows you to compare corresponding rows with each other.
2. Using the intermediary function Countif to compare data
To address the situation in Example 1, you can utilize the Countif function as an intermediary to compare values in Column 1 against those in Column 2 for duplicates.
Description of Countif Function
The Countif function counts the number of cells that meet a certain condition within the selected data range.
Syntax
COUNTIF(Range, Criteria)
Where:
- Range: The data range containing the data to be counted, which is a mandatory parameter.
- Criteria: The condition used to count the data, which is a mandatory parameter.
Consider Example 2
Comparing data between 2 Columns, marking values present in Column 1 but not in Column 2 and vice versa.
Step 1: You name the data columns -> Then select all data in Column 1 into the address bar and name it list1 -> press Enter.
Step 2: Similarly, you name Column 2, then the names of the two data columns are displayed:
Step 3: You select all of list1 -> then go to the Home tab -> select Conditional Formatting -> select New Rule...
Step 4: The dialog box appears -> you select Use a formula to determine which cells to format:
Step 5: You input the formula =COUNTIF(list1,B3)=0 then click Format:
Step 6: The Format Cells interface appears -> you click on the Fill tab -> select a color not in list2 -> then click OK:
Step 7: Next, you click OK to close the dialog box -> the result, colors differentiating the list1 from list2 are applied:
Step 8: Similarly with list2 you perform the same steps, but only different in inputting the formula: = COUNTIF(list1,D3)=0 meaning it colors cells with value 0 (not matching)
As a result, you have compared two data columns and can add notes for others to easily visualize:
Above is the sharing about how to use comparison functions in Excel. To use these comparison functions, you can utilize the EXACT function or use COUNTIF function to compare data in Excel. Wish you success!
