find gaps in sequence

schurers

New Member
Joined
Nov 25, 2008
Messages
5
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!!!!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Code:
=if(b2=b1,if(a2< (a1+2),"OK","GAP!!!"),"OK")
and copy down as far as required.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Code:
=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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
Code:
=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

New Member
Joined
Nov 25, 2008
Messages
5
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Nov 25, 2008
Messages
5
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Nov 25, 2008
Messages
5
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Code:
=a3=a4
should return TRUE.

Also, do you have recalculation set to automatic ?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,365
Messages
5,444,030
Members
405,261
Latest member
Khauff

This Week's Hot Topics

Top