Need to flag an entry that is a duplicate of a previous entry that includes time

beccab

I have created a solution for our staff to reserve desks when they are coming in to the office. They select the desk, the day and if they want it for the AM, PM or All Day. By using formulas and helper columns I can get the solution to let a person know when their reservation is double booked based on the desk and date combination. However, when I add in the time, I can only get it to work for a couple of the variables. Ideally, I would like to be able to send an automation about double booking for the following scenarios:

• Person 1 has it reserved all day and person 2 reserves it for all day
• Person 1 has it reserved all day and person 2 reserves it for morning
• Person 1 has it reserved all day and person 2 reserves it for afternoon
• Person 1 has it reserved for morning and person 2 reserves it for all day
• Person 1 has it reserved for morning and person 2 reserves it for morning
• Person 1 has it reserved for afternoon and person 2 reserves it for all day
• Person 1 has it reserved for afternoon and person 2 reserves it for afternoon

I have the following columns to already set up that work.

Date Check - if the reserved helper column is checked, then star this column if the created date at the row is the newest. =IF(Reserved@row = 1, IF(Created@row = MAX(COLLECT(Created:Created, Reservation:Reservation, Reservation@row)), 1, 0))

Reservation - combines the desk name, reservation date and time frame into a single value. =JOIN([Desk name]@row:[Time frame]@row, ",")

Reserved - if the count of the reservation at the row is greater than 1, flag the field. =IF(COUNTIF(Reservation:Reservation, Reservation@row) > 1, 1, 0)

Time Frame column is a dropdown with Morning, Afternoon or All Day.

The following formula got me part of the way.

I'm open to additional helper texts, multiple formulas and/or advanced features such as Bridge.

KPH

What you have looks great. Rather than a more complex formula, you could add a helper column for morning and have that checked if Morning or All Day is selected, and another for afternoon and have that checked if Afternoon or All Day is selected. (You could even offer those as the form options and get rid of the current drop down - if someone wants all day, they chose morning and afternoon). Then you can carry on with simple matching.

Brian_Richardson

Agree with KPH - I don't think you would find that Bridge does much more here than what you could do simply with a formula and adjusting the morning and afternoon flags. Bridge could iterate through the sheet and do the comparisons, but so can the formulas you've already setup.