Flagging events that share dates

kstrobush

I'm struggling to come up with a way to alert if any other events in the sheet share any dates in common. I'd like to use the "Other Events?" checkbox column to flag if matches are found. And, if possible from there, add another column that lists all the events that have any matches.

In the example screenshot, the Dates (All) column is just a Join of the Date 1 :Date 3 information

Genevieve P.

Hi @kstrobush

You can use a COUNTIF formula to check if the count of that date is "more than 1", meaning it's in any other row.

=IF(COUNTIF([Date 1]:[Date 1], [Date 1]@row) > 1, 1, 0)

However, since you have 3 columns, you'll need to have 9 statements all added together:

• Date in Column 1 cell, checking in 3 columns
• Date in Column 2 cell, checking in 3 columns
• Date in Column 3 cell, checking in 3 columns

We'll also need to make sure blank cells aren't counted…so we add in some IF statements before the COUNTIFs.

Try something like this:

=IF(SUM(COUNTIF([Date 1]:[Date 1], [Date 1]@row) + COUNTIF([Date 2]:[Date 2], [Date 1]@row) + COUNTIF([Date 3]:[Date 3], [Date 1]@row) + IF([Date 2]@row <> "", COUNTIF([Date 1]:[Date 1], [Date 2]@row), 0) + IF([Date 2]@row <> "", COUNTIF([Date 2]:[Date 2], [Date 2]@row), 0) + IF([Date 2]@row <> "", COUNTIF([Date 3]:[Date 3], [Date 2]@row), 0) + IF([Date 3]@row <> "", COUNTIF([Date 1]:[Date 1], [Date 3]@row), 0) + IF([Date 3]@row <> "", COUNTIF([Date 2]:[Date 2], [Date 3]@row), 0) + IF([Date 3]@row <> "", COUNTIF([Date 3]:[Date 3], [Date 3]@row), 0)) > 1, 1, 0)

Cheers,
Genevieve

kstrobush

Hello @Genevieve P.

I think I found a parallel path to a solution last week that I think takes the long way around what you were able to put together in a single formula, and hadn't had a chance to come back here to share. I think it's working, but maybe you can see if there are any holes in my logic!

I added three helper columns "count date 1", "count date 2", "count date 3", and used:

=COUNTIF([Dates (All)]:[Dates (All)], HAS(@cell, [Date #]@row))

Then in my checkbox column used:

=IF([count date 1]@row > 1, 1, IF([count date 2]@row > 1, 1, IF([count date 3]@row > 1, 1, 0)))

I'm trying to get to the next part, identifying which Events have shared dates.

I have added two new columns, Shared Dates, and Events with Shared Dates. I can get the Shared Dates column to work, using:

=JOIN(COLLECT([Date 1]@row, [count date 1]@row, >1)) + CHAR(10) + JOIN(COLLECT([Date 2]@row, [count date 2]@row, >1)) + CHAR(10) + JOIN(COLLECT([Date 3]@row, [count date 3]@row, >1))

I'm trying to use that to list every Event that has a date in common in "Events with Shared Dates", but the results aren't quite right.

Row 1 is returning the right results (I'm fine that the results for the row includes its own Event, but if there's a way it NOT be included so that it's just a list of any OTHER Events sharing dates with this one, that would be perfect), but in Row 2 I would expect to see Test Event 1 AND Test Event 2 listed, since they both have 6/01/24 in common. Same with Row 3, which should be returning Test Events 1 and 3.

=JOIN(COLLECT(Event:Event, [Shared Dates]:[Shared Dates], HAS([Dates (All)]@row, @cell)), CHAR(10))

Genevieve P.

Hey @kstrobush

Well-done with the parallel path! Yes, that works. 🙂 No flaws in the logic that I can see.

Have you figured this one out yet? You look very close! In this instance you have 2 scenarios:

• a mutli-select cell with multiple values, looking into a column range where you are looking for individual matches to each value
• a multi-select cell with one value, looking into a column range for a match

Each of these instances need a different HAS structure:

• HAS([Dates (All)]@row, @cell) for the multi-select cell
• HAS(@cell, [Dates (All)]@row) for the single value

Here's how I would write the formula, with a Substitute at the beginning to replace the current row's task with a blank value:

=SUBSTITUTE(JOIN(COLLECT(Event:Event, [Shared Dates]:[Shared Dates], HAS(@cell, [Dates (All)]@row)), CHAR(10)) + JOIN(COLLECT(Event:Event, [Shared Dates]:[Shared Dates], HAS([Dates (All)]@row, @cell)), CHAR(10)), Event@row, "")

I've added it into a multi-select cell so that duplicates are ignored:

Cheers,
Genevieve