Excel – Find duplicates in two different tabs

microsoft excelspreadsheet

This is what I would like to do: Tab A Column A has a mass of numbers, Tab B Column B will have numbers inputted into it. I need to have the Tab B Column B highlight showing a match to a number in Tab A or have a 'Yes' or 'No' answer appear in Tab B column A indicating a match. Any ideas how to do this?

Best Answer

I'm not sure whether you want to check for a duplicate on the corresponding row in Sheet A or whether any cell in Column A on Sheet A has the value in the corresponding row in Sheet B. I'm assuming your first sheet is named Sheet1, the second sheet is named Sheet2, and both data sets start on the first row of their sheet.

Testing for duplicates in the entire column

Place =IF(COUNTIF(Sheet1!A:A,B1)>0,"YES","NO") in A1 on Sheet2. It will say "YES" or "NO" depending on whether any cell in column A on Sheet1 has the value in B1 on Sheet2. Drag the cell down to adjust it for more rows.

How it works: COUNTIF returns the number of cells in a range that satisfy a condition. The range Sheet1!A:A includes all cells in column A of Sheet1. They are tested for equality with B1 (which will be adjusted when you drag the cell). The IF function returns a value depending on the value of its condition, which is whether the number of cells from COUNTIF is nonzero.

Highlighting duplicates from the entire column

Added in response to a comment.

To highlight based on the presence of duplicates, conditional formatting needs to be applied. Click on the B column header in the second sheet to select the entire column, then choose Manage Rules under the Conditional Formatting button. Add a new rule with the type "Use a formula to determine which cells to format." Press the Format button to select what formatting will be applied. (Highlights can be done with the Fill tab.) Place =(COUNTIF(Sheet1!A:A,B1)>0) in the formula box, then click OK.

Testing for duplicates in the corresponding row

Place =IF(Sheet1!A1=B1,"YES","NO") in A1 on Sheet2. Again, it says "YES" or "NO" and you can drag it down to more rows.

How it works: the = between Sheet1!A1 (which refers to A1 on Sheet1) and B1 tests whether those cells' values are equal. IF functions the same as above.

Related Question