Flagging a duplicate reference across 2 sheets

Question

I have an intake sheet for users to submit a New Supplier Request. This generates a New Supplier Document. Once created, this entry moves to an archive sheet

There will be scenarios where someone will submit a request for a New Supplier, of which a document already exists: I want to eliminate this issue as I cant have two documents for the same Supplier

I would need a 'Duplicate' checkbox on intake which contains a formula to 'LOOKUP' the 'Supplier Name' field and return a 'yes/checked' to stop the automation taking place.

Any ideas of what that formula would be?

heyjay · Accepted Answer

For the duplicate column, you can set that to a checkbox field then use the following formula

=IFERROR(IF(MATCH([Supplier Name]@row, [Supplier Name]:[Supplier Name], 0), 1, 0), 0)

or

=IF(COUNTIF([Supplier Name]:[Supplier Name], [Supplier Name]@row) > 1, 1, 0)