Grouping overlapping times in MS Excel

[BBxcl]

Hi,
I have a spreadsheet that contains multiple entries each with a start and end time. I want to be able to identify overlapping time entries and then be able to group the overlapping entries by perhaps assigning a Overlap Group ID to pairs of overlapping entries. Start and End Times can be any time during the day. The reason I want to group them is so that I’m able to identify overlapping times in a big dataset, “group” each overlapping time so that I can extract each “group” from the dataset and carry out further calculations on each overlapping “group”.

Please see some sample entries and what I want to obtain in the image below:

I have managed to workout the "Overlap Exists" with the following function

=IF(SUMPRODUCT((startTime<=endTime)*(endTime>=startTime)),TRUE,FALSE)

However, I’m struggling to determine how to group the two overlapping entries as shown in my example above.

Any help would be greatly appreciated!

 

[Peter_SSs]

What would you want here?
ID 1 overlaps with ID 2 and ID 2 overlaps with ID 1 and ID 3, but ID 1 and ID 3 do not overlap at all.

21 11 14.xlsm
	A	B	C
1	ID	Start	End
2	1	9:00	12:00
3	2	11:00	15:00
4	3	14:00	14:30
Overlap

 

[BBxcl]

Hmm, in that case I’d want all entries to be extracted. Although, I think using “overlap ID” to group wouldn’t really work very well there so perhaps we could have a dynamic function to show row numbers of overlapping entries and that could generate multiple row numbers?

So in the scenario presented, I would assign row number 2 to ID 1, row numbers 1 and 3 to ID 2 and row number 2 to ID 3.