Hi Folks,
Firstly here is a sample of the data:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Account ID[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000T25KYAAZ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/14/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000NJ0e2AAD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/15/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000Li6AmAAJ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]1/18/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000KPgMOAA1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/15/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000KPgJJAA1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/14/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000T25KYAAZ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000NJ0e2AAD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/16/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000Li6AmAAJ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]1/19/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000KPgMOAA1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/16/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
What I'm trying to do is write an error checking formula to tell me if any records with the same account ID have overlapping date ranges. Ideally the formula would also list the rows where this occurs but I'd also be fine with a separate column that indicates "overlap" or "good" - something like that.
I can't for the life of me figure out the best way to do this. I tried using nested IF formulas to see if the account IDs are equal then to see if the end date of each row is greater than the start date of other rows who match the ID. This keeps giving me an error though and I think it's because I have a misunderstanding of array formulas.
I highlighted an example of something I want to catch, in red.
If possible I'd like just one cell that says "ERRORS" if it finds any overlapping dates for the same accounts, OR i'd be fine with a whole separate column that would have a formula you pull down until the last row. Either way is fine.
If anyone has any suggestions I'd be eternally grateful! Thanks.
Firstly here is a sample of the data:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Account ID[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000T25KYAAZ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/14/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000NJ0e2AAD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/15/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000Li6AmAAJ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]1/18/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000KPgMOAA1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/15/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000KPgJJAA1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]10/14/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000T25KYAAZ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/1/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000NJ0e2AAD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/16/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000Li6AmAAJ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]1/19/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD="width: 252"]0015000000KPgMOAA1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl158, width: 174, align: right"]10/16/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl158, width: 168, align: right"]9/30/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
What I'm trying to do is write an error checking formula to tell me if any records with the same account ID have overlapping date ranges. Ideally the formula would also list the rows where this occurs but I'd also be fine with a separate column that indicates "overlap" or "good" - something like that.
I can't for the life of me figure out the best way to do this. I tried using nested IF formulas to see if the account IDs are equal then to see if the end date of each row is greater than the start date of other rows who match the ID. This keeps giving me an error though and I think it's because I have a misunderstanding of array formulas.
I highlighted an example of something I want to catch, in red.
If possible I'd like just one cell that says "ERRORS" if it finds any overlapping dates for the same accounts, OR i'd be fine with a whole separate column that would have a formula you pull down until the last row. Either way is fine.
If anyone has any suggestions I'd be eternally grateful! Thanks.