microsoft excel find duplicates in two spreadsheets

Microsoft excel find duplicates in two spreadsheets how to#
Microsoft excel find duplicates in two spreadsheets download#

) | 40+ Practical Formulas ( Calculate age based on birthday. Top 15 Toolsets: 12 Text Tools ( Add Text, Remove Characters. Super Lookup: Multiple Criteria VLookup | Multiple Value VLookup | VLookup Across Multiple Sheets | Fuzzy Lookup .Īdvanced Drop-down List: Quickly Create Drop Down List | Dependent Drop Down List | Multi-select Drop Down List .Ĭolumn Manager: Add a Specific Number of Columns | Move Columns | Toggle Visibility Status of Hidden Columns | Compare Ranges & Columns .įeatured Features: Grid Focus | Design View | Big Formula Bar | Workbook & Sheet Manager | Resource Library (Auto Text) | Date Picker | Combine Worksheets | Encrypt/Decrypt Cells | Send Emails by List | Super Filter | Special Filter (filter bold/italic/strikethrough.) . Popular Features: Find, Highlight or Identify Duplicates | Delete Blank Rows | Combine Columns or Cells without Losing Data | Round without Formula . In the popping dialog, select the two ranges in the Find values in and According to boxes, check Each row option and Same values option, go to specify the Fill backcolor and Fill font color as you need.Ĭlick Ok, a dialog pops out to remind you the number of selected rows, click OK to close it.

Microsoft excel find duplicates in two spreadsheets download#

Tip: In the formula, A2, B2 is the first name and last name, Sheet7 is the sheet you want to compare to.Īfter installing Kutools for Excel, please do as below: （Free Download Kutools for Excel Now!)Ĭlick Kutools > Select > Select Same & Different Cells. Then drag fill handle down to fill the formula to cells.Īs below screenshot shown, the blank results indicate duplicate values, X’s are the unique values. In Sheet 6, select the cell next to the names, type this formula =IF(ISNUMBER(MATCH(A2&B2,Sheet7!A$2:A$100&Sheet7!B$2:B$100,0)),'','X'), press Shift + Ctrl + Enter keys. If you want to compare two ranges as below screenshot shown, you can do as these You can check Select entire rows option to select the entire row.Ĭlick Ok, dialog pops out to remind you the number of duplicates. In the popping dialog, check Duplicates (Except 1 st one), or All duplicates (Including 1st one) as you need, then check Fill backcolor or Fill font color to select a color to highlight the duplicate values. Select the combined names, click Kutools > Select > Select Duplicate & Unique Cells. Select the format you want to highlight duplicates from the values with list. Select the combined names, then click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the next column of the first names and last names, type this formula =E2&' '&F2, drag fill handle down to combine all names.

If the first name and last name in separate columns, you need to combine them first, then find the duplicate names.

Microsoft excel find duplicates in two spreadsheets how to#

In this article, I talk about how to find the duplicate names which both match first name and last name in two cases.Ĭase 1 find duplicate names while the first name and last name in separate columnsĬase 2 compare two ranges and find duplicate names while the first name and last name in separate columns How to find and highlight the duplicate names which both match first name and last name in Excel?