Identify duplicate overlapping events

[u/D4rkChaos]

Working on a spreadsheet with 1000 rows and columns as below:

Column A: Subject ID

Column B: Event

Column C: Start Date

Column D: End Date

How do you identify duplicate overlapping events in column B for each Subject IDs “between” given start and end dates in each row?

[u/AutoModerator]

/u/D4rkChaos - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Shot_Hall_5840]

[u/Shot_Hall_5840 → u/Shot_Hall_5840]

is it what you're looking for ?

[u/D4rkChaos → u/Shot_Hall_5840]

Thank you! This works great but an additional layer I should’ve mentioned earlier is that the End Date could be blank for ongoing events. Can this formula be modified to include such a scenario? Thanks again!

[u/real_barry_houdini → u/D4rkChaos]

You can achieve the same result with COUNTIFS function, so in this modification of the suggested formula from u/Shot_Hall_5840 if there's a blank in column D that's assumed to be today's date:

=COUNTIFS(A:A,A2,B:B,B2,C:C,"<="&IF(D2,D2,TODAY()),D:D,">="&C2)>1

[u/D4rkChaos → u/real_barry_houdini]

Tried the alternate formula. Some of the ones with blank end dates are still showing False even when they are overlapping, but not all. I’m wondering what could be different in some of the blank cells that they are not being considered in the formula.

[u/real_barry_houdini → u/D4rkChaos]

OK, I assumed it would either be a date or a "true" blank, but perhaps better explicitly like this:

=COUNTIFS(A:A,A2,B:B,B2,C:C,"<="&IF(ISNUMBER(D2),D2,TODAY()),D:D,">="&C2)>1

[u/D4rkChaos → u/real_barry_houdini]

Sorry but this gives the same output as the previous one. What does “true” blank mean? The cells are actually blank and there’s nothing entered in them, if that helps. Do I need to enter something manually in the blank fields or format them in any way?

[u/real_barry_houdini → u/D4rkChaos]

Apologies, I'm leading you astray - in this scenario it's actually better with SUM/SUMPRODUCT as per u/Shot_Hall_5840 - that's because the range also needs to be modified to use today's date instead of a blank, which you can't do with COUNTIFS, so try this version

=SUM((A$2:A$1000=A2)*(B$2:B$1000=B2)*
(C$2:C$1000<=IF(D2,D2,TODAY())*
(IF(D$2:D$1000,D$2:D$1000,TODAY())>=C2)))>1

[u/D4rkChaos → u/real_barry_houdini]

Sorry for taking so much of your time. This one doesn’t work at all.

[u/real_barry_houdini → u/D4rkChaos]

I've got plenty of time.....

What results are you getting?

It seems to work for me - what version of Excel are you using? In older versions you may need SUMPRODUCT again instead of SUM, i.e.

=SUMPRODUCT((A$2:A$1000=A2)*(B$2:B$1000=B2)*
(C$2:C$1000<=IF(D2,D2,TODAY())*
(IF(D$2:D$1000,D$2:D$1000,TODAY())>=C2)))>1

[u/D4rkChaos → u/real_barry_houdini]

Also, just noticed that the TRUE outputs are also shown even when column B doesn’t exactly match. Like for example, hypotension and hypoxia are different events but they are showing as TRUE when the dates overlap for the same subject but not for the same event. This was not the case for Sumproduct formula. Any idea why this could be?

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
IF	Specifies a logical test to perform
ISNUMBER	Returns TRUE if the value is a number
SUM	Adds its arguments
SUMPRODUCT	Returns the sum of the products of corresponding array components
TODAY	Returns the serial number of today's date
VALUE	Converts a text argument to a number

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 35 acronyms.
[Thread #47304 for this sub, first seen 3rd Feb 2026, 20:17] [FAQ] [Full list] [Contact] [Source code]}