How can I identify Duplicate entries across two sheets?

KMan1190

I have two sheets with overlapping information. I already have a formula that identifies duplicate clients within each sheet individually by returning a number for how many times in the sheet that client name shows up. The formula we use for this is =COUNTIFS([Name Helper]:[Name Helper], [Name Helper]@row)

Since the clients should not be located in both sheets, I am looking for a way to identify if a client is located in both sheets (IE if the client name is found in this sheet only, return value 1, if the client name is found in this sheet and the sister sheet, return value 2). I'd then resolve by moving/deleting the duplicate row. What formula can I use to identify to identify if the client is found in this sheet and in the sister sheet or just in this sheet?

Jeff Reisman

@KMan1190

You can use pretty much the same formula, just change the range to be the client column in the other sheet. As you type your COUNTIFS formula, when this pops up, click on "Reference Another Sheet", browse to the other sheet, and click on the header of Client column. This creates the reference to that range. Then continue your formula the same as the one you used locally. So it will look something like this:

=COUNTIFS({Other Sheet Range 1}, [Name Helper]@row)

You can embed this inside an IF if you want to set a value based on the results:

=IF(COUNTIFS({Other Sheet Range 1}, [Name Helper]@row) > 0, 2, 1)

In English - If the countifs result is more than 0, set this cell to 2, otherwise, set it to 1.