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
microsoft excelspreadsheet
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 namedSheet2
, 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 rangeSheet1!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). TheIF
function returns a value depending on the value of its condition, which is whether the number of cells fromCOUNTIF
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
=
betweenSheet1!A1
(which refers to A1 on Sheet1) andB1
tests whether those cells' values are equal.IF
functions the same as above.