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?
Excel – Find duplicates in two different tabs
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
=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:
COUNTIFreturns the number of cells in a range that satisfy a condition. The range
Sheet1!A:Aincludes all cells in column A of Sheet1. They are tested for equality with B1 (which will be adjusted when you drag the cell). The
IFfunction returns a value depending on the value of its condition, which is whether the number of cells from
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
=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
Sheet1!A1(which refers to A1 on Sheet1) and
B1tests whether those cells' values are equal.
IFfunctions the same as above.