Compare 2 columns highlight duplicates ..

[FriendlyGardener]

Hi there

I am still learning heaps about excel and have so many projects on the go..

This seems a simple ask but I have spent hours ..


I have 2 sheets in a book. I want to compare one column only, and highlight duplicates. there are 22000 rows in one sheet and much less in the other. I wish the Column in the large sheet to have the highlight.


eg sheet 1 column A (has 22000 rows) It is here I need to know which cells in A are also in Sheet 2 A ( I thought highlighting was a good Idea but it could be some other way, maybe better if the word "duplicate" could be added?.. whatever is easiest)


sheet 2 column A


Would greatly appreciate suggestions!

 

[Hui]

G'Day Friendly Gardener


Try the following 2 options on Row 2 of page 2

=+IF(MATCH(A2,Sheet1!$A$1:$A$22000)>0,"Duplicate","Unique")

or

=+IF(SUMPRODUCT(1*(Sheet1!$A$1:$A$6=Sheet2!A2))>0,"Duplicate","Unique")

and copy down to the end of the data


You can then Copy this column and paste as values and then sort the data to get all the Unique or Duplicate values together

 

[FriendlyGardener]

Thanks Hui, I will try this at work tommorrow, its night here in Australia

I am so glad I found this site..

 

[FriendlyGardener]

"Try the following 2 options on Row 2 of page 2

=+IF(MATCH(A2,Sheet1!$A$1:$A$22000)>0,"Duplicate","Unique")

or

=+IF(SUMPRODUCT(1*(Sheet1!$A$1:$A$6=Sheet2!A2))>0,"Duplicate","Unique")

and copy down to the end of the data


You can then Copy this column and paste as values and then sort the data to get all the Unique or Duplicate values together "


I tried this and it works on sheet 2.. but I really need the word Duplicate to appear on sheet 1, is there any way to do this?

 

[Hui]

copy this to Sheet1 B2 and copy down

=+IF(SUMPRODUCT(1*(Sheet1!A2=Sheet2!$A$1:$A$2000))>0,"Duplicate","Unique")

Change A$2000 to suit your data range on Sheet 2

 

[FriendlyGardener]

"copy this to Sheet1 B2 and copy down

=+IF(SUMPRODUCT(1*(Sheet1!A2=Sheet2!$A$1:$A$2000))>0,"Duplicate","Unique")

Change A$2000 to suit your data range on Sheet 2 "


Dear Hui

You are so kind this works so well, I am learning so much my head is spinning..