I have a single spreadsheet with two sheets. Sheet1 contains columns called Part No. and Duplicate. Sheet2 contains a column called Part No. I am looking to build a rule that will see if there are duplicate part numbers between Sheet1 and Sheet1, and if there is, insert a "Yes" into the relevant row of the Duplicate column of Sheet1.

I know using something like a would highlight duplicates, but I think from a presentation perspective a "Yes" in its own column would be better. However, I can't find how to do it.

Cheers.

CodePudding user response：

I have sheet "StackA1" with column A as "Part No." and column B as "Duplicate". I also have sheet "StackA2" with column A as "Part No."

In "StackA1" cell B2, I have =IF(ISERROR(VLOOKUP(A2,StackA2!A:A,1,FALSE)),"","Yes") and then I copy that formula to the cells below it.

The output I see is that if a Part No. in StackA1 column A has a duplicate in StackA2 column A, then the cell next to it in column B says "Yes", otherwise it is blank.

The VLookup function looks for the Part No in StackA2 column A, and if it finds an exact match, then it returns the part number, otherwise it returns an error (#N/A). So, if the returned value is an error, we display an empty string, otherwise we display "Yes".