How to find duplicates in two different excel workbooks

Answer

To find duplicates in two different Excel workbooks, use COUNTIF formulas, VLOOKUP or MATCH functions, Power Query, or conditional formatting with external references. These methods require specific syntax referencing the external file path (e.g., '[Workbook2.xlsx]Sheet1'!$B$5:$B$15) and typically need both workbooks open simultaneously, except for Power Query which loads data independently.

What methods does Excel offer to find duplicates between two workbooks?

Excel provides four primary methods to identify duplicates between two different workbooks:

1. COUNTIF formula: Counts matches from the second workbook in the first using external reference syntax (e.g., =COUNTIF('[Workbook2.xlsx]Sheet1'!$B$5:$B$15, B5)>0). Returns TRUE for duplicates and works well for simple flagging tasks.
2. VLOOKUP or MATCH with ISERROR: Checks whether a value exists in the other workbook and labels items directly (e.g., =IF(ISERROR(VLOOKUP(B5,'[Workbook2.xlsx]Sheet1'!$B$5:$B$15,1,0)),"Unique","Duplicate")).
3. Power Query: Imports both sheets, merges on key columns, and filters for matches. This method handles large datasets without formulas and allows files to close afterward.
4. Conditional formatting: Applies visual highlighting using a formula-based rule with COUNTIF referencing the external workbook.

Key difference from same-workbook methods

Workbook methods demand explicit file paths and open files, creating risk of breakage when paths change. Same-workbook methods use simpler internal syntax like Sheet2!$B$5:$B$15 and support direct tools like Arrange All for side-by-side viewing. Power Query bridges both scenarios by loading external data non-destructively.

How to use COUNTIF to find duplicates between two workbooks

To use COUNTIF for finding duplicates between two workbooks, create a helper column with a formula that references the external file using bracket syntax. Both workbooks must remain open for the formula to function.

Prerequisites

1. Open both workbooks (e.g., Workbook1.xlsx as the primary file and Workbook2.xlsx as the reference file).
2. Ensure data lists align by key columns, such as IDs in column B of Sheet1 for both workbooks.

Enter the formula

1. In Workbook1, select a new column next to your data (e.g., column C in Sheet1, starting at C5).
2. Enter the formula: =COUNTIF('[Workbook2.xlsx]Sheet1'!$B$5:$B$15,B5)>0
3. Adjust the workbook name, sheet name, and range to match your data.
4. Press Enter.
5. TRUE indicates a duplicate match exists in Workbook2; FALSE indicates a unique value.

Copy the formula

1. Select the cell containing the formula.
2. Drag the fill handle down to cover all rows with data.
3. Review results by sorting or filtering for TRUE values.

Preserve results

1. Copy the helper column results.
2. Use Paste Special > Values to freeze them as static data.
3. This prevents reference breakage when Workbook2 closes.

Label duplicates with text

For text labels instead of TRUE/FALSE, use this formula: =IF(COUNTIF('[Workbook2.xlsx]Sheet1'!$B$5:$B$15,B5)>0,"Duplicate","Unique")

How to use VLOOKUP or MATCH to find duplicates between two workbooks

To use VLOOKUP or MATCH for finding duplicates between two workbooks, create a helper column with a formula wrapped in IF and error-handling functions. Both workbooks must remain open during use.

Prerequisites

1. Open both workbooks (e.g., Workbook1.xlsx for lookup values in column B of Sheet1, Workbook2.xlsx for the reference range in column B of Sheet1).
2. Align data by the matching column in both files.

VLOOKUP method

1. In Workbook1 Sheet1, select a helper column (e.g., C5 next to data starting at B5).
2. Enter the formula: =IF(ISERROR(VLOOKUP(B5,'[Workbook2.xlsx]Sheet1'!$B$5:$B$15,1,FALSE)),"Unique","Duplicate")
3. Press Enter.
4. Drag the formula down the column to apply to all rows.
5. "Duplicate" flags matches in Workbook2; "Unique" indicates non-matches.

MATCH method

1. In the same helper column position, enter: =IF(ISNA(MATCH(B5,'[Workbook2.xlsx]Sheet1'!$B$5:$B$15,0)),"Unique","Duplicate")
2. Press Enter.
3. Drag down to apply the formula to all rows.
4. MATCH returns the position of the value or an error, enabling precise existence checks without column indexing requirements.

Differences between VLOOKUP and MATCH

VLOOKUP requires the lookup column to be first in the specified range. MATCH scans any single column flexibly without this restriction. Both methods require absolute references ($ signs) and both files open to avoid #REF! errors. Paste results as values afterward for stability.

How to use Power Query to find duplicates between two workbooks

To use Power Query for finding duplicates between two workbooks, load and merge external data through the Power Query Editor. This method handles large datasets efficiently without keeping files open and supports multiple key columns for precise matching.

Load the primary workbook data

1. Open Workbook1.
2. Select the data range in Sheet1.
3. Go to Data > From Table/Range.
4. Confirm table creation when prompted.
5. The Power Query Editor opens with your data loaded.

Load the reference workbook data

1. In the Power Query Editor or Excel, go to Data > Get Data > From File > From Workbook.
2. Navigate to and select Workbook2.
3. Select Sheet1 from the Navigator window.
4. Click Transform Data to load the query into the editor.

Merge queries for duplicates

1. In the Power Query Editor with Workbook1 data active, select the key column(s) for matching (e.g., ID column).
2. Go to Home > Merge Queries.
3. Select the Workbook2 query from the dropdown.
4. Select the matching column(s) in the Workbook2 table.
5. Set Join Kind to Inner to return duplicates only.
6. Click OK.

Output results

1. Expand the merged table to include desired columns from Workbook2.
2. Click Close & Load to output matches to a new sheet in Workbook1.
3. Right-click the query and select Refresh to update results after data changes.

Why Power Query excels for this task

Power Query handles massive datasets efficiently without formula breakage or open-file requirements. It supports multiple key columns for precise matching and preserves original data while enabling automation through the refresh function.

Can conditional formatting highlight duplicates between two workbooks?

Yes, conditional formatting can highlight duplicates between two different workbooks using a formula-based rule with COUNTIF, but both workbooks must remain open for the formatting to function.

Apply conditional formatting

1. Open both workbooks (e.g., Workbook1 as primary, Workbook2 as reference).
2. In Workbook1, select the range to check (e.g., Sheet1 B5:B15).
3. Go to Home > Conditional Formatting > New Rule.
4. Select "Use a formula to determine which cells to format."
5. Enter the formula: =COUNTIF('[Workbook2.xlsx]Sheet1'!$B$5:$B$15,B5)>0
6. Adjust workbook name, sheet name, and range as needed.
7. Use a relative reference for B5 (no $ before the row number).
8. Click Format and select a fill color (e.g., yellow or red).
9. Click OK twice to apply the rule.
10. Duplicates matching values in Workbook2 now highlight automatically.

Limitations of this method

1. Both files must remain open, or links break with #REF! errors.
2. The method does not work across closed workbooks, unlike Power Query.
3. Highlighting is visual only and does not extract or list duplicate data.
4. Formulas may slow performance on large datasets.
5. Paste formatting results or convert to static formatting after use for portability when sharing files.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report