Clicky

I have a sample of a file where because of erroneous input, some of the start date and end dates are overlapping.  I have identified the two records where the dates are overlapping in this scenario.

However, I have a file with thousands of records where this can happen.  Is there a way to program this in VB, formulas , or any way possible to identify these records?

asked 12/13/2011 10:59

morinia's gravatar image

morinia ♦♦


7 Answers:
Hi, morinia.

I don't understand your definition of "overlapping". For example, Row 3 is shown as not overlapping, whereas Row 5 is.

Regards,
Brian.
link

answered

redmondb's gravatar image

redmondb

Are you able to sort the data differently? By start date would be good!

If sorted you could do a simple date comparison of current start date with previous record finish date.

Once sorted, starting in D2 formula would be:

=IF(B2<=C1,"Y","")

Copy down as required.

Thanks
Rob H
link

answered 2011-12-14 at 07:47:57

robhenson's gravatar image

robhenson

Hey Brian!

Row 3 doesn't overlap with anything, though, does it?

Try this formula in D2 copied down

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

That identifies any row which overlaps with any other, 4 to 9 on the example, see attached

regards, barry
link

answered 2011-12-14 at 07:56:30

barryhoudini's gravatar image

barryhoudini

Barry,

I was having a slow day! When I realised that it was from row to row and went to post, Rob had beaten me to it.

Regards,
Brian.
link

answered 2011-12-14 at 08:03:02

redmondb's gravatar image

redmondb

I added an extra condition for checking the member_Id in column A was the same.  The sample file I sent only had one member_id.

=IF(A2=A1,IF(B2<=C1,"Y",""),"")

Thanks for your help
link

answered 2011-12-14 at 08:15:21

morinia's gravatar image

morinia

Hello morinia,

My suggestion takes the ids into account and also works on sorted or unsorted data - it's going to be a little less efficient though. With your modification of Rob's suggestion that might only identify the first instance of many where there's an overlap, e.g. if you have a period from 7th to 14th December followed by another period of 8th to 9th Dec and then 10th to 11th and 14th to 17th then all of those last 3 periods overlap with the 1st one, but the formula will only identify the second one as an overlap.

regards, barry
link

answered 2011-12-14 at 08:44:13

barryhoudini's gravatar image

barryhoudini

I split the points.  I had to give points to the first answer because when I posted the example I did not mention there were multiple ID's.
link

answered 2011-12-14 at 12:15:50

morinia's gravatar image

morinia

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/13/2011 10:59

Seen: 162 times

Last updated: 12/16/2011 12:09