## Identify overlapping records

*Hi Oscar,*

*Great website! Keep up the good work.*

*I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in Row 4 overlap their work schedule. I would like to know if there were a way, say for instance in Range E6:E12, to display Jeff’s Row number next to Shaun’s name and visa – versa. I can see how this will easily display what record numbers overlap when there is only one overlap in the range. Can we differentiate the records when there are two overlaps? Let’s say Theodor in Row 6 also has an overlap with Thomas in Row 2.*

*Now the data becomes confusing because we have to determine, from the 4 grayed rows, who overlaps with whom.*

*Can a formula return the Row value of the “matching” overlap record?*

* In Jeff’s E6 cell it would indicate “Row 4” as to the matching record, and the reverse for Shaun. Shaun’s E9 cell would indicate “Row 1” as the matching record.*

* And at the same time Theodor’s E11 cell would reflect “Row 2” for Thomas’s record and Thomas’ E7 cell would show “Row 6” for Theodor.*

*I haven’t even touched on the tougher one, such as what happens when John in Row 7 has an end date of 2010-01-07, thus overlapping with both Jeff and Shaun!*

*One step at a time. :)*

*Thanks*

**Array formula in cell D6:**

**How to enter an array formula**

- Copy above array formula (Ctrl + c)
- Double click on cell D6
- Paste above array formula (Ctrl + v)
- Press and hold CTRL + SHIFT
- Press Enter

**How to copy array formula**

- Select cell D6
- Copy cell D6 (Ctrl + c)
- Select cell range E6:F6
- Paste (Ctrl + v)
- Select cell range D6:F6
- Copy (Ctrl + c)
- Select cell range D7:F12
- Paste (Ctrl + v)

### Explaining array formula in celll D6

**Step 1 - Compare current date range with other date ranges**

($B6<=$C$6:$C$12)*($C6>=$B$6:$B$12)

**Step 2 - Create an array containing row numbers except the current row number**

IF(ROW($B$6:$B$12)=ROW(), "", ROW($B$6:$B$12)), "")

**Step 3 - If current date range overlaps another date range, return row number**

IF(($B6<=$C$6:$C$12)*($C6>=$B$6:$B$12), IF(ROW($B$6:$B$12)=ROW(), "", ROW($B$6:$B$12)), "")

**Step 4 - Find the k-th smallest row number**

SMALL(IF(($B6<=$C$6:$C$12)*($C6>=$B$6:$B$12), IF(ROW($B$6:$B$12)=ROW(), "", ROW($B$6:$B$12)), ""),COLUMN(A1))

### Download excel *.xlsx file

Identify-overlapping-date-ranges.xlsx

### Functions in this article

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SMALL(**array,k**)**

Returns the k-th smallest number in this data set.

**ROW(**reference**)**

Returns the rownumber of a reference

### Category: Overlapping

How to highlight overlapping date ranges (Excel 2007) Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use […]

Comments(31) Filed in category: Excel, Overlapping

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

Comments(24) Filed in category: Excel, Overlapping

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Comments(23) Filed in category: Excel, Overlapping

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

Comments(8) Filed in category: Dates, Excel, Overlapping

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Comments(7) Filed in category: Excel, Overlapping

Bryan asks: i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart) i have […]

Comments(6) Filed in category: Excel, Overlapping

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In […]

Comments(4) Filed in category: Overlapping

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

Comments(4) Filed in category: Excel, Overlapping

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional […]

Comments(2) Filed in category: Conditional formatting, Excel, Overlapping, VBA

### 7 Responses to “Identify overlapping records”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Thanks! Took a little tweaking, but this does just what I need.

For this, I use Sumproduct with two conditions:

1. Current start date = Start data range

mma173,

Can you tell us the formula?

Hi again Oscar!

On to Step 2!

The people using this file are not "Tech" savey, in fact they don't like Excel at all. So I'm trying to make it easier for them to use. The values in D6:F12 are understandable to me, however "Non-Tech Bob" comes and ask's, "How can this cell(D6) show a 9 when there are only 7 names?"

The easiest solution would be to insert a new column at the beginning of the file and starting in cell A6, enter the text "Line 1".

A7 "Line 2"

A8 "Line 3" and so on..

Is there a way for the formula in D6 (now E6) to return the text string in column A rather than the ROW number?

(New

Column)

A B C D E F G H

LINE # NAME: START DATE: END DATE: Overlapping with lines:

Line 1 Jeff 2010-01-04 2010-01-08 Line 4 Line 7

Line 2 Thomas 2010-01-11 2010-01-15

Line 3 Tim 2010-01-18 2010-01-22

Line 4 Shaun 2010-01-07 2010-01-09 Line 1

Line 5 Simon 2010-01-25 2010-01-29

Line 6 Theodor2010-02-01 2010-02-05

Line 7 John 2010-01-03 2010-01-04 Line 1

This way I could hide the "Row and Column" headers under Options and reduce the clutter on the page.

Thanks again for your assistance!

Boy did THAT not work!

This should help.

https://s11.postimg.org/jgjaowhqr/Identifying_Overlap_Ranges.png

cwrbelis,

Is there a way for the formula in D6 (now E6) to return the text string in column A rather than the ROW number?Sure! There are two sheets in the workbook below.

Identify-overlapping-date-rangesv2.xlsx