Excel 2010: Overlapping Dates Flag

[emacleod]

Hello,

This is my first post to this site. Most of what I have learned about Excel has come from Bill Jelen’s books, podcast and information from this site. For that , I thank everyone here!

Here is my issue which I would deeply appreciate any assistance which you could kindly offer:

I have a spreadsheet in Excel 2010 with 50k+ rows of data. I need a formula which I can place in the last column of this spreadsheet which will flag any records based on the following criteria: This would be for any record which has the same Customer, with the same Service Code with a different Service Provider where the Service Dates (Start & End) overlap each other.

Below is a subset of data from the 50k+file. The first four rows at the top meet the criteria I have outlined above. This is for two rows per each occurrence: Rows 1-2 are for the same Customer ID, they have the same Service Code, the Service Dates (Start & End) overlap each other and the Provider ID is different. Rows 3-4 also meet this criteria. Essentially, I would like to have a formula which would go in the last column (Overlap Flag) which would flag records meeting the aforementioned criteria—like the occurrences I have referenced here. The Overlap Flag could say, “Overlap” for those records and be blank for all records not meeting the requested criteria.

Please let me know if there is any additional information you may need.

Thank You In Advance!

~emacleod

Here is my sample (Columns A - F [from left-to-right]):

[TABLE="width: 542"]
<tbody>[TR]
[TD]Customer ID
[/TD]
[TD]Service Start
[/TD]
[TD]Service End
[/TD]
[TD]Provider ID
[/TD]
[TD]6
[/TD]
[TD]Overlap Flag
[/TD]
[/TR]
[TR]
[TD]201400297397721
[/TD]
[TD]Jan 18, 2014
[/TD]
[TD]Nov 4, 2014
[/TD]
[TD]598017824
[/TD]
[TD]Y3422
[/TD]
[TD]Overlap
[/TD]
[/TR]
[TR]
[TD]201400297397721
[/TD]
[TD]Feb 27, 2014
[/TD]
[TD]Jan 5, 2015
[/TD]
[TD]431043372
[/TD]
[TD]Y3422
[/TD]
[TD]Overlap
[/TD]
[/TR]
[TR]
[TD]201402043262256
[/TD]
[TD]Apr 1, 2015
[/TD]
[TD]Sep 1, 2015
[/TD]
[TD]226205047
[/TD]
[TD]F2314
[/TD]
[TD]Overlap
[/TD]
[/TR]
[TR]
[TD]201402043262256
[/TD]
[TD]May 1, 2015
[/TD]
[TD]Dec 1, 2015
[/TD]
[TD]882420153
[/TD]
[TD]F2314
[/TD]
[TD]Overlap
[/TD]
[/TR]
[TR]
[TD]201403605011436
[/TD]
[TD]Dec 14, 2013
[/TD]
[TD]Jun 14, 2014
[/TD]
[TD]772151417
[/TD]
[TD]F2314
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]201404135548165
[/TD]
[TD]Jan 1, 2014
[/TD]
[TD]Jun 30, 2014
[/TD]
[TD]538009348
[/TD]
[TD]W3219
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]201404947587974
[/TD]
[TD]Dec 16, 2013
[/TD]
[TD]Jun 14, 2014
[/TD]
[TD]319972930
[/TD]
[TD]F2314
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]201405194673608
[/TD]
[TD]May 9, 2015
[/TD]
[TD]May 13, 2015
[/TD]
[TD]440082829
[/TD]
[TD]F2314
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]201405202786969
[/TD]
[TD]Dec 17, 2013
[/TD]
[TD]Jun 17, 2014
[/TD]
[TD]106413106
[/TD]
[TD]F2314
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Chrisdontm]

There is a lot thrown together here, but try this formula...

If something doesn't work we should be able to tweak it...

This goes in F2 and gets copied on down;

=IF(AND(COUNTIF($A:$A,A2)>1,B2<C2,COUNTIF(E:E,E2)>1,COUNTIF(D:D,D2)<2),"← Overlap (ROW # "&IF(MATCH(A2,A:A,0)<>ROW(),MATCH(A2,A:A,0),MATCH(A2,A3:A99998,0)+ROW())&")","")

I put in an extra note about locating the duplicate in the formula, we can take it out if you like...

 

[emacleod]

Brilliant Solution to my problem—thank you so much! Also, nice touch with the addition of the arrow character w/Row reference!

Just one tweak, please. Could you alter the formula to include the following:
Column E of the sample I pasted represents the Service Code field. My fault, has it appears I mislabeled the column with the number ‘6’ as opposed to correctly labeling it, Service Code. As part of the needed criteria, the Service Code also needs to be the same in order for the record to be flagged as an Overlap. This will complete the criteria which needs to be accounted for: “Any record which has the same Customer ID, with the same Service Code (the Column mislabeled '6' of my pasted sample above) with a different Provider ID where the Service Dates (Start & End) overlap each other. “

Once again, I sincerely appreciate this!
~emacleod

 

[Chrisdontm]

I actually had that mislabeled column withing the formula, and as I was working on that part about the rows I must have wiped it out...Oops...

Also that part about the dates was partially wiped out...Oops...

This should work;

Again this should go in F2 and copied on down...

=IF(AND(COUNTIF($A:$A,$A2)>1,$B2<$C2,COUNTIF($E:$E,$E2)>1,COUNTIF($D:$D,$D2)<2),"← Overlap (ROW # "&IF(MATCH($A2,$A:$A,0)<>ROW(),MATCH($A2,$A:$A,0),MATCH($A2,$A3:$A$100000,0)+ROW())&")","")

Hey a neat trick I learned in making that arrow character;

↑ ↓ → ← To make these, hold the Alt button down, and type 24, then let go of the Alt button, same for 25, 26, and 27 respectively...

 

[emacleod]

AWESOME! Thank you so much!

~emacleod