Highlight Duplicates between Sheet1 and Sheet2, ignoring duplicates already in Sheet1

[KariSaga]

I have been searching for a formula how I can highlight duplicate data between two different worksheets but ignoring duplicates on one of the worksheets. I have tried a few formulas and macros but not getting the results I want. Hoping someone here is able to help.

For a better understanding, I have a database which has multiple worksheets (eg. sheet1, sheet2, etc...), in the first worksheet it has 10k+ rows, and multiple columns with different kinds of data, lets call this worksheet "Sheet1". The other worksheets are not really important. I will create a new worksheet and give it a name, then I will copy and paste some data into this worksheet which will expand across several columns and rows, lets call this worksheet "Sheet2". In Sheet1 I want to focus only on column "I" and in Sheet2 I will want to look at columns A thru Z (or entire worksheet).

I am looking for a way to highlight duplicates between Sheet1 and Sheet2, but I do not want to highlight duplicates already in Sheet1 column I, just highlighting the duplicates found in Sheet2.

 

[etaf]

have you tried a countif()

select sheet 1 columns I

then add a formula in conditional formatting

=COUNTIF(Sheet2!$A:$G,$I1)
so if a value exists in sheet2 that matches value in sheet1 it will highlight

 

[KariSaga]

This works perfectly for Sheet1, however I am also trying to get those in Sheet2 highlighted that were duplicate in Sheet1 column I

 

[etaf]

ok, then I think in sheet 2
=COUNTIF(Sheet1!$I:$I,sheet2!A1)

 

[KariSaga]

Perfect, thank you so much!

 

[etaf]

you are welcome