CODEWITHSUNDEEP
excelexcel-2010
user2862611
user2862611

Reputation: 13

find the row where dates overlap Excel 2010

I have two sets of date ranges and have used sumproduct to identify which date ranges in the first 2 columns overlap with any of the date ranges in the second two columns

what I need now is to identify which row the overlap in the second date ranges occurs so as you can see below

TK  30/03/2015  24/04/2015  22/12/2014  21/01/2015      TRUE
TK  20/04/2015  24/04/2015  19/05/2015  21/01/2015      FALSE
TK  27/04/2015  15/05/2015  22/04/2015  04/04/2015      TRUE
TK  18/05/2015  20/05/2015  05/02/2015  09/02/2015      FALSE
TK  21/05/2015  22/05/2015  10/02/2015  11/02/2015      FALSE
TK  25/05/2015  25/05/2015  12/02/2015  12/02/2015      FALSE

The formula used in G is 

=SUMPRODUCT((A2=$A$2:$A$5)* ((B2<=$E$2:$E$5)* (C2>=$D$2:$D$5)+ (D2<=$C$2:$C$5)* (E2>=$B$2:$B$5)))>0

The columns used are A- G As you can see Row 3 Dates overlap with row 1 dates

What I need to do is get the row numbers for the overlap dates this is eluding me I wonder if anyone could help

regards

JM

Hello again

hit a snag

N/A appears when I expand the Range area

any ideas what I am doing wrong

thanks again Jo

Upvotes: 1

Views: 500

Answers (2)

user4039065
user4039065

Reputation:

If you are trying to make this an all-in-one formula, add a ROW(...) function directly into your SUMPRODUCT.

        Which row

The formula in G2 is,

=SUMPRODUCT(ROW($2:$7)*(A2=$A$2:$A$7)*((B2<=$E$2:$E$7)*(C2>=$D$2:$D$7)+(D2<=$C$2:$C$7)*(E2>=$B$2:$B$7)))

That will show the actual row on the worksheet. If you prefer to indicate the row within the data block, substitute ROW($1:$6) for ROW($2:$7).

Upvotes: 0

Erik Rasmussen
Erik Rasmussen

Reputation: 331

If you put the below around your formula, it will return the row number whenever it currently returns TRUE

=IF(forumla=TRUE,ROW(A2),0)

your column G should now look like this

2 0 4 0 0 0

I hope this helps

Erik

Upvotes: 1

Related Questions