duplicate entry checking 2 sheets

Lynde Munoz

I have an invoice intake that pulls into an 'In Process Invoices' sheet.

Once the invoice gets to a certain approval point it moves to another sheet 'Completed invoices'

I need to ensure that there is a notification/color change/something that notes that an invoice number is a duplicate.

i.e. once the new invoice is entered into the intake sheet, it checks the invoice number against both the current sheet AND the 'completed' sheet. Any advice?

Andrée Starå

https://community.smartsheet.com/discussion/comment/490899#Comment_490899

Try something like this.

1️⃣ . Add an Autonumber column (if you don't have one already). I've named mine RowID.

2️⃣ . Add a column called Duplicate and make it a Flag type column.

3️⃣ . Add the below formula and make it a Column Formula.

=IF([Invoice Number]@row <> "", IF(RowID@row <> INDEX(COLLECT(RowID:RowID, [Invoice Number]:[Invoice Number], [Invoice Number]@row ), 1), 1, 0))

This formula will flag any duplicates after the first.

Did that work/help?

✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏