Google-sheets – How to detect duplicates in two worksheets

conditional formattingfilterformulasgoogle sheets

I have 1 Google Sheet with 2 worksheets:

Worksheet "1" has 1 column A with 5 lines of data (AAA, AAB, AAC, AAD, AAE)
Worksheet "2" has 1 column A with 5 lines of data (AAA, BBB, CCC, DDD, EEE)

I would like to automatically detect that AAA is, in fact, a duplicate since it exists in both worksheets. I would like to detect this in worksheet "2".

I understand this should be done by using Conditional Formatting but I don't know how.

Best Answer

=FILTER(A2, COUNTIF(INDIRECT("sheet1!A2:A"), A2))

Related Solutions

Google-sheets – Create a dropdown list of options from VLOOKUP

Why not have a column that concatenates ACC and UNQ#, like:-

AAA-1000
AAA-1002

Then when AAA was typed in you would get the two options, then you can use SPLIT to get the UNQ# to do the second vlookup.

Google Sheets – Conditional Format Formula for Previous Cell Content

The answer to the question you linked contains the answer to this one. But since it apparently didn't get through, here it is again:

In conditional formatting, you enter the formula as it should apply to the upper left corner of the formatted range. The spreadsheet will apply it to the rest of the range using relative references. So, if the upper left corner of formatted range is C4, then

=B4 means "the value in the cell to the left of this one".
=$B4 means "the value of the cell in B column of this row"
=C$1 means "the value at the top of this column"
=$B$2 means "the value in cell B2"

Best Answer

Related Solutions

Google-sheets – Create a dropdown list of options from VLOOKUP

Google Sheets – Conditional Format Formula for Previous Cell Content

Related Topic