Formula to find duplicates in any column on a sheet

Yvette Moreland

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!

Andrée Starå

https://community.smartsheet.com/discussion/123392/formula-to-find-duplicates-in-any-column-on-a-sheet

Hi,

I hope you're well and safe!

Try something like this.

=IF(COUNTIFS([Day, Date, Area, Time, Type]:[Day, Date, Area, Time, Type], <>"", [Day, Date, Area, Time, Type]:[Day, Date, Area, Time, Type], [Room 1]@row) > 0, 1, IF(COUNTIFS([Day, Date, Area, Time, Type]:[Day, Date, Area, Time, Type], <>"", [Day, Date, Area, Time, Type]:[Day, Date, Area, Time, Type], [Room 2]@row) > 0, 1))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Yvette Moreland

Hi Andrée Starå 

Thanks for the response.

Your formula works great for duplicates in the same row, which I will use elsewhere now that I have it!

For this project, however, I need the formula to find duplicates anywhere on the sheet. Data will be entered using a form, so submissions will populate on new rows in the Room columns, which is different from the Day, Date, Area, Time, Type column that include all options for all rooms.

Thanks,

Andrée Starå

https://community.smartsheet.com/discussion/comment/441065#Comment_441065

Excellent!

Happy to help!

It should work in the whole sheet if you convert it to a column formula.

Did that work?

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Yvette Moreland

Hi,

If set as a column formula, it does find duplicates on the entire sheet. However, it is entering 1 for the Delivery Complete on the form submission row, but not the schedule row. The schedule row is the first time on the sheet that the item will be found, and where I need it. It is okay if it is on both rows, but at least on the schedule row. I hope that makes sense.

Yvette Moreland

Hi Andrée Starå 

Circling back on this project.

If set as a column formula, it does find duplicates on the entire sheet. However, it is entering 1 for the Delivery Complete on the form submission row, but not the schedule row. The schedule row is the first time on the sheet that the item will be found, and where I need it. It is okay if it is on both rows, but at least on the schedule row. I hope that makes sense.

Any change you could take another look?