![]() Note: If you want to highlight duplicate values along with two workbooks, this method will be highly beneficial for you. Shortly, you’ll get the highlighted duplicates values as shown in the following screenshot. 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.Īfter pressing OK, you’ll get another input box immediately to insert the cell range of Workbook2. 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. Lastly, I assigned the VBA RGB function to highlight the color. Subsequently, I ran the For loop to find the duplicates in those workbooks. Then, I utilized the InputBox to insert the cell range for Workbook1 and Workbook2 respectively. ⧭ In the above code, I declared necessary variables first. Set RngWorkbook2 = Application.InputBox("Range2:", "Insert Cell Range", Type:=8) Set RngWorkbook1 = Application.InputBox("Range1:", "Insert Cell Range", Type:=8) Sub Duplicates_Workbooks_VBA()ĭim RngWorkbook1 As Range, RngWorkbook2 As Range, Rn1 As Range, Rn2 As Range Then, copy the following code into the newly created module. So, you have to create a module to use the VBA Code.įirstly, open a module by clicking Developer > Visual Basic. Using the VBA Code to Find Duplicates in Two Excel Workbooksīesides, you might use the VBA code to find the duplicates in two different workbooks as well as to highlight the duplicate values. Read More: How to Find Duplicate Values in Excel Using Formula (9 Methods)ĥ. Here, TRUE represents “ Duplicates” and FALSE represents “ Unique” records. Lastly, the ISNUMBER function is used to display TRUE instead of showing the number and FALSE for the #N/A error. Otherwise, it will return the #N/A error. ⧭ In this formula, the MATCH function finds the relative position in the numeric value e.g. Utilizing ISNUMBER and MATCH Functionsįurthermore, the ISNUMBER function combined with the MATCH function can be used to show duplicates. How to Find Duplicates without Deleting in Excel (7 Methods)Ĥ.How to Find Duplicate Rows in Excel (5 Quick Ways).Excel Find Duplicates in Column and Delete Row (4 Quick Ways).How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods).How to Compare Two Excel Sheets Duplicates (4 Quick Ways).Read More: How to Vlookup Duplicate Matches in Excel (5 Easy Ways) Shortly, you’ll get the following output. Finally, the IF function provides the output as “ Duplicates” or “ Unique”. Therefore, the ISERROR function is used to avoid any display of errors. ⧭ In the above formula, the VLOOKUP function returns the Employee Name if it finds similar in Workbook2 (duplicates). 1 is the col_index_num argument and 0 is for approximate matching. Here, B5 is the starting cell of Workbook1.
0 Comments
Leave a Reply. |