Formula to find duplicates in any column on a sheet


I have a sheet with food delivery schedules. I want to use a form for staff to submit that the delivery was completed. I need a way to show on a report and dashboard, what has been delivered in real time.

The set up needs to be easy for the user. I have Day, Date, Room, Time, Type in separate columns for them to enter, then a helper column that combines those columns. I will use the helper column to copy/past as dropdown options for each Room on my form.

The staff will see ONLY the Room 1, Room 2 (and additional rooms), columns. Submissions will populate at the bottom of this same sheet.

I need a column formula in the [Delivery Complete] column that will change it to 'Yes' on the schedule row when there is a submission in any column that matches the [Day, Date, Area, Time, Type] column. The formula generating 'Yes' must be on the schedule row, but it could also be on the submission row if that makes it easier for a column formula. Users will be adding rows, so the column formula is important, if possible.

Thanks in advance!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!