find gaps in sequence

[schurers]

I have a file that contains addresses in column C. I need to find any gaps in addresses. Ex:
211 Corbin Dr
213 Corbin Drive
214 Corbin Drive
123 Apple Drive
124 Apple Dr
124 Apple Dr
127 Apple Drive

I want to identify that there is a gap between 211 Corbin and 213 Corbin and 124 and 127 Apple Drive.

Currently the address including house number are in column c. However, I split the house number into a separate column and the street address in yet another column. There are also duplicates which I have identified by using conditional formatting to highlight the duplicates. Thanks in advance!!!!

 

[Gerald Higgins]

How do you define a gap ?
Is it anything where the street name is the same, but the difference between house numbers is more than 1 ?

If so, maybe something like this.
Assumptions -
1) house number is in Col A, starting row 1,
2) street name is in Col B (adapt as required if this is not the case).
3) data is sorted, first by street name, then by house number.
Put this in C2

=if(b2=b1,if(a2< (a1+2),"OK","GAP!!!"),"OK")

and copy down as far as required.

 

[Gerald Higgins]

By the way, my solution won't identify the gap between
124 Apple Dr
and
127 Apple Drive

Is it possible for you to standardise the street names ?

Alternatively, you could use a simplified version of my formula, like this

=if(a2< (a1+2),"OK","GAP!!!")

but this would also identify a gap in this scenario, which you might not want to do
12 Apple Drive
15 Banana Street

 

[Gerald Higgins]

Alternatively, if you knew that you could reliably match the first few characters of the street name, you could do something like this.
For example, let's say that you knew that if all the characters in the street name before the first space character, plus the first character after the space all matched, then you could treat them as being the same street.

For example
Apple Dr would give Apple D
Apple Drive would also give Apple D.
Therefore treat them as a match.

Use a variant of my first solution.

=if(left(b2,find(" ",b2&" ")+1)=left(b1,find(" ",b1&" ")+1),
if(a2< (a1+2),"OK","GAP!!!"),"OK")

This will also match, for example,
Apple Gardens
Apple Grove
so you might need to be careful.

 

[schurers]

I liked the first solution but I am having a bit of difficulty. Not quite sure why the the last line shows a GAP (can we check for duplicates as well). Thanks again in advance. I appreciate your assistance greatly!
<TABLE style="WIDTH: 257pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=342 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7826" width=214><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: yellow" width=64 height=20>714 </TD><TD class=xl64 id=td_post_1789139 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 161pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: yellow" width=214>18TH ST</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: yellow" height=21>209 </TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: yellow">SAINT ANDREW CT</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">OK


</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: yellow" height=21>214 </TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: yellow">SAINT ANDREW CT</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">GAP!!!


</TD></TR>


<TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: yellow" height=21>214 </TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: yellow">SAINT ANDREW CT</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">GAP!!!</TD></TR></TBODY></TABLE>

 

[Gerald Higgins]

Your data in post #5 seems to have empty rows between the data. Is this right ?
I don't understand what's going on there.

 

[schurers]

No empty rows in the data (just the way it copied over) 714 is in cell A1. last entry is on row 4. Formula is in cell c2.

 

[Gerald Higgins]

I can't replicate this.
Using your data, I get
<TABLE style="WIDTH: 216pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=288 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 4608" width=144><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=72 height=17 x:num>714</TD><TD id=td_post_1789139 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 108pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=144>...18th Street</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>209 </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">...Saint Andrew Ct</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(LEFT(B2,FIND(" ",B2&" ")+1)=LEFT(B1,FIND(" ",B1&" ")+1),IF(A2<(A1+2),"OK","GAP!!!"),"OK")'>OK</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>214</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">...Saint Andrew Ct</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(LEFT(B3,FIND(" ",B3&" ")+1)=LEFT(B2,FIND(" ",B2&" ")+1),IF(A3<(A2+2),"OK","GAP!!!"),"OK")'>GAP!!!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>214</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">...Saint Andrew Ct</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:fmla='=IF(LEFT(B4,FIND(" ",B4&" ")+1)=LEFT(B3,FIND(" ",B3&" ")+1),IF(A4<(A3+2),"OK","GAP!!!"),"OK")'>OK</TD></TR></TBODY></TABLE>

 

[schurers]

I know I am not feeling well but I must really be out of the loop. Here are my formulas starting in Cell c2:
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 240pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=320 colSpan=5 height=21>=IF(B2=B1,IF(A2< (A1+2),"OK","GAP!!!"),"OK") result ok


</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 id=td_post_1789139 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=21>=IF(B3=B2,IF(A3< (A2+2),"OK","GAP!!!"),"OK")
result Gap


</TD></TR>


<TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=21>=IF(B4=B3,IF(A4< (A3+2),"OK","GAP!!!"),"OK")
result Gap
</TD></TR></TBODY></TABLE>

 

[Gerald Higgins]

Yeah, that's what I've got too.

So, you have 214 in A3, and 214 in A4 as well, correct ?

Can you do a test to see if A3 equals A4, such as

=a3=a4

should return TRUE.

Also, do you have recalculation set to automatic ?