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! 🙏

Andrée Starå

https://community.smartsheet.com/discussion/141164/duplicate-entry-checking-2-sheets

Hi,

I hope you're well and safe!

You can use a COUNTIFS structure to check for a match, or you can use an INDEX/MATCH combination.

https://help.smartsheet.com/function/countifs

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ 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! 🙏

Lynde Munoz

I gave up on two sheets, condensed to one sheet, I can countifs but it won't indicate that it is a duplicate.

The AI is no help.

Andrée Starå

https://community.smartsheet.com/discussion/comment/490894#Comment_490894

Ok.

Please share some screenshots and/or additional information.
(Delete/replace any confidential/sensitive information before sharing.) That would make it easier to 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! 🙏

Lynde Munoz

OK, ultimately (and i don't know if it is even possible) I would like to check the primary column 'Invoice Number' for duplicates and if it is a duplicate, mark the 'duplicate' row with something like a flag or red, or something very visible. Because of the number of invoices we have at any given time, it needs to be very visible.

I keep coming up with a circular error.

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! 🙏

Lynde Munoz

THANK YOU!!! that works. I really appreciate all your help. :)

Andrée Starå

https://community.smartsheet.com/discussion/comment/490949#Comment_490949

Excellent!

You're more than welcome!

✅ 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! 🙏