Date range and duplicate values

Mikejbreen

Hi,

We are trying to use Smartsheet to manage our engineers. At the moment, an engineer can only be on one project at a time. Is there a way to search a Start and Finish column and see if an engineer's initials appear twice anywhere within that Start and Finish range? I've attached a screenshot of how it looks currently:

(Apologies, I've removed the client from here so the titles may look a bit odd!)

What we want to do is easily see if an engineer is double booked across any date range. At the moment, we have up to 8 columns where that engineer's name could appear (Eng1 - Eng8 respectively).

Any help would be much appreciated! Thanks,

Mike

Genevieve P.

Hi @Mikejbreen

Yes, we can combine it into one formula, but we will still need to reference each individual column to search for the individual values in the helper Total Eng column.

The reason is it's a multi-select, so when you search for  [Total Eng]@row within [Total Eng]:[Total Eng], it will only find a duplicate if all the selections have a duplicate...so all 2 or all 3 Eng would need to be repeated. It's matching the entire value of the cell, versus parsing it out individually and searching through each cell for that one value. Does that make sense?

We can make a long nested IF statement in one flag column, like so:

=IF(OR(COUNTIFS(formula 1) >1, COUNTIFS(formula 1) >1, COUNTIFS(formula 1)>1...etc

So for three columns:

=IF(OR(COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng1]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1, COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng2]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1, COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng3]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1), 1, 0)

Let me know if that works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Mikejbreen

This is a little tricky because of the number of individual Eng columns to look through for duplicates. I presume JC could be put in Eng1 or Eng2 or Eng8, and won't always be in the first column, is that correct?

The way I would manage this then is to add in a number of other columns. The first column I'd add in is a Multi-Select column where you can combine all the values that appear in your Eng columns into one cell.

To do this, we'll use a JOIN formula:

=JOIN([Eng1]@row:[Eng8]@row, CHAR(10))

Then I would set up a number of Flag columns at the end of your sheet. You will need one per Eng column to indicate which person is listed twice, like so:

The formula in my first column is:

=IF(COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng1]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1, 1, 0)

We're searching the Total Eng column for the person in the Eng1 cell, then comparing the dates. If their name appears more than once within that date range, we're creating a Red Flag for that row, in the associated Eng column.

Then the Eng2 Flag column is:

=IF(COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng2]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1, 1, 0)

You just need to change out the value we're searching for.

In my screen capture above I also created a couple of Conditional Formatting rules to highlight in red the cells that are associated with the Flag, so I can quickly see which is which. We need all the Eng columns with the names together in a row for the JOIN formula, so we can't put the flag right next to the associated column.

You could of course highlight the whole row, or the bar in the Gantt chart if that's more helpful for your process.

Cheers!

Genevieve

Mikejbreen

Hi Genevieve,

This is excellent, thank you. Is there a way to condense this so that we have just the one flag that is marked red if duplicates are found in the [Total Eng] column with clashing date ranges? We don't need to do it per column (Eng1 - Eng8) respectively as it should be pretty clear to the viewer where the clashes lie. I've tried the formula as follows:

=IF(COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Total Eng]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1, 1, 0)

But it seems inconsistent if it seems a clash. Sometimes I'm getting a red flag, other times not. It looks like it might be looking for the order in which the Total Eng column is populated. It's putting the names alphabetically and if there's not an exact match, there's no flag. Sorry if I'm missing something very obvious!

Thanks again,

Mike

Genevieve P.

Hi @Mikejbreen

Yes, we can combine it into one formula, but we will still need to reference each individual column to search for the individual values in the helper Total Eng column.

The reason is it's a multi-select, so when you search for  [Total Eng]@row within [Total Eng]:[Total Eng], it will only find a duplicate if all the selections have a duplicate...so all 2 or all 3 Eng would need to be repeated. It's matching the entire value of the cell, versus parsing it out individually and searching through each cell for that one value. Does that make sense?

We can make a long nested IF statement in one flag column, like so:

=IF(OR(COUNTIFS(formula 1) >1, COUNTIFS(formula 1) >1, COUNTIFS(formula 1)>1...etc

So for three columns:

=IF(OR(COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng1]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1, COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng2]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1, COUNTIFS([Total Eng]:[Total Eng], HAS(@cell, [Eng3]@row), Start:Start, <=Finish@row, Finish:Finish, >=Start@row) > 1), 1, 0)

Let me know if that works for you!

Cheers,

Genevieve

Mikejbreen

Hi Genevieve,

It worked like a charm - thank you so much for your help. I've been trying to suss that out for days!

Genevieve P.

Wonderful! I'm so glad it worked for you. 😊

Happy holidays!