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