# How to Find Duplicates in Two Different Excel Workbooks (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, you might need to find duplicates in two different workbooks for identifying, modifying, or even removing those duplicates. In this article, I’m going to show you 5 methods on how to find duplicates in two different Excel workbooks with proper explanation. So that you may adjust the methods to your dataset.

## 5 Methods to Find Duplicates in Two Different Excel Workbooks

Let’s assume, we have two different datasets with a common field (Employee Name) in two different sheets and certainly in two workbooks. The first dataset is in Sheet1 of the Employee Information1.xlsm workbook (Workbook1). The other one is in Sheet1 of the Employee Information2.xlsx workbook (Workbook2) as shown in the following screenshot. Now, you need to find the duplicates in those different worksheets.

Let’s check out the following methods.

### 1. Using the COUNTIF Function

In the beginning method, you’ll see the use of the COUNTIF function, one of the most popular functions, to count the number of cells with given criteria.

While counting duplicates, the generic formula will be like the following.

`=COUNTIF(Range, Criteria)`

In the case of finding duplicates in two different workbooks, the adjusted formula will be-

`=COUNTIF('[Employee Information2.xlsx]Sheet1'!\$B\$5:\$B\$15,B5)`

Here, ‘[Employee Information2.xlsx]Sheet1’!\$B\$5:\$B\$15 is the range where I want to count duplicates. Needless to say that the ‘[Employee Information2.xlsx]Sheet1’ represents the Sheet1 of Workbook2 and B5 is the specific cell that I want to count in cells B5 through B15 at the Workbook2 (criteria).

After inserting the formula, press the ENTER key. And you’ll get the following output if you use the Fill Handle Tool. If you look closely at the above output, you’ll find that the value of duplicates is 1. On the other hand, the value of unique records is 0.

### 2. Using IF and COUNTIF Functions

Instead of getting the numerical value, you also can find the string “Duplicates” or “Unique” if you want. For accomplishing this, you have to assign the logical IF function with the previous function.

`=IF(COUNTIF('[Employee Information2.xlsx]Sheet1'!\$B\$5:\$B\$15,B5:B15),"Duplicate","Unique")`

⧭ If the value of the output using the COUNTIF function is greater than 0, the IF function will return “Duplicates”. Else it’ll return “Unique”. Note: here, I inserted the criteria as a range B5:B15. In such a situation, you don’t need to copy the formula for the below cells. Rather, you’ll get the output by pressing ENTER only. ### 3. Applying the VLOOKUP Function to Find Duplicates in Two Workbooks

The VLOOKUP function together with the IF and IFERROR function may be used to get the duplicates in two different Excel workbooks.

`=IF(ISERROR(VLOOKUP(B5,'[Employee Information2.xlsx]Sheet1'!\$B\$5:\$B\$15,1,0)),"Unique", "Duplicate")`

Here, B5 is the starting cell of Workbook1. 1 is the col_index_num argument and 0 is for approximate matching.

⧭ In the above formula, the VLOOKUP function returns the Employee Name if it finds similar in Workbook2 (duplicates). Else, it returns the #N/A error. Therefore, the ISERROR function is used to avoid any display of errors. Finally, the IF function provides the output as “Duplicates” or “Unique”. Hit ENTER and drag down the formula. Shortly, you’ll get the following output. ### 4. Utilizing ISNUMBER and MATCH Functions

Furthermore, the ISNUMBER function combined with the MATCH function can be used to show duplicates. The combined formula is-

`=ISNUMBER(MATCH(B5,'[Employee Information2.xlsx]Sheet1'!\$B\$5:\$B\$15,0))`

⧭ In this formula, the MATCH function finds the relative position in the numeric value e.g. 4 for the B8 cell. of the matched lookup value. Otherwise, it will return the #N/A error. Lastly, the ISNUMBER function is used to display TRUE instead of showing the number and FALSE for the #N/A error. So, the output will be as follows. Here, TRUE represents “Duplicates” and FALSE represents “Unique” records.

### 5. Using the VBA Code to Find Duplicates in Two Excel Workbooks

Besides, you might use the VBA code to find the duplicates in two different workbooks as well as to highlight the duplicate values.

So, you have to create a module to use the VBA Code.

Firstly, open a module by clicking Developer > Visual Basic. Secondly, go to Insert > Module. Then, copy the following code into the newly created module.

``````Sub Duplicates_Workbooks_VBA()
Dim RngWorkbook1 As Range, RngWorkbook2 As Range, Rn1 As Range, Rn2 As Range
Set RngWorkbook1 = Application.InputBox("Range1:", "Insert Cell Range", Type:=8)
Set RngWorkbook2 = Application.InputBox("Range2:", "Insert Cell Range", Type:=8)
For Each Rn1 In RngWorkbook1
Rn1Value = Rn1.Value
For Each Rn2 In RngWorkbook2
If Rn1Value = Rn2.Value Then
Rn1.Interior.Color = VBA.RGB(255, 255, 0)
Exit For
End If
Next
Next
End Sub`````` ⧭ In the above code, I declared necessary variables first. Then, I utilized the InputBox to insert the cell range for Workbook1 and Workbook2 respectively. Subsequently, I ran the For loop to find the duplicates in those workbooks. Lastly, I assigned the VBA RGB function to highlight the color. Specifically, I used 255 as the value of the red and green arguments and 0 for the blue argument to highlight duplicates values in yellow color.

Next, if you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get the input box to insert the cell range of Workbook1. After pressing OK, you’ll get another input box immediately to insert the cell range of Workbook2. Shortly, you’ll get the highlighted duplicates values as shown in the following screenshot. Note: If you want to highlight duplicate values along with two workbooks, this method will be highly beneficial for you. Because the Conditional Formatting tool doesn’t work for two workbooks. Rather it is handly for highlighting duplicates across multiple sheets in a workbook.

## Conclusion

That’s the end of today’s session. I strongly believe that from now you may find duplicates in two different Excel workbooks. Anyway, if you have any queries or recommendations, please share them in the comments section below.

## Related Articles Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  