Flagging events that share dates
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
Answers
-
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 -
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))
