SOUNDEX y FUZZY VLOOKUP FOR VBA EXCEL 201107.docx

SOUNDEX y FUZZY VLOOKUP FOR VBA EXCEL 201107 VBA] Function St!Co"#$%t1 A% Stin&' %t2 A% Stin&( A% Dou)*+ , , +tun% - nu")+ %.o/in&  co"#-i%on )+t/++n t/o n-"+% , , i+ St!Co"#$A1' B1( , , Fo"-t c+** -% P+c+nt-&+ to "-3+ it *oo3 #+tty44 , , Di" 5tc.6)*$100' 100( Di" 5y5- A% Dou)*+' 6.i%5- A% Dou)*+ Di" i A% 8nt+&+' 9 A% 8nt+&+' ii A% 8nt+&+' 99 A% 8nt+&+ :it. :o3%.++tFunction %t1;  6i";$Po#+$%t1;(( %t2;  6i";$Po#+$%t2;(( En< :it. 5y5-=  0 Fo i  L+n$%t1;( 6o 1 St+# >1 Fo 9  L+n$%t2;( 6o 1 St+# >1 8? 5i<;$%t1;' i' 1(  5i<;$%t2;' 9' 1( 6.+n 6.i%5-=  0 Fo ii  $i @ 1( 6o L+n$%t1;( Fo 99  $9 @ 1( 6o L+n$%t2;( 8? 5tc.6)*$ii' 99(  6.i%5-= 6.+n 6.i%5-=  5tc.6)*$ii' 99( En< 8? N+t 99 N+t ii 5tc.6)*$i' 9(  6.i%5-= @ 1 8? $6.i%5-= @ 1(  6.i%5-= 6.+n 5y5-=  6.i%5-= @ 1 En< 8? En< 8? N+t 9 N+t i St!Co"#  5y5-=  $$L+n$%t1;( @ L+n$%t2;((  2( En< Function VBA]

++% t.+ <+t-i*% Pu)*ic Function VFuGGyLoo3u#$Loo3u#!V-*u+ A% Stin&' 6-)*+!A-y A% V-i-nt(

Fin< t.+ )+%t "-tc. ?o - &iH+n %tin& in co*u"n 1 o? -n --y o? <-t- o)t-in+< ?o" -n Ec+* -n&+ 6.i% i% ?unction-**y %i"i*- to VLoo3u#' )ut it +tun% t.+ )+%t "-tc.' not t.+ ?i%t +-ct "-tc. 6.i% ?unction i% not c-%+>%+n%itiH+

•

8? you <-t- Iu-*ity i% #oo' you -+ -"-tc.in& %co+ -n<
•

U%+ VFuGGyLoo3u#!P.-%+ i? you -+ tyin& to "-tc. - #.-%+ o %+nt+nc+ $%+Iu+nc+ o? /o<% %+#--t+< )y %#-c+%( -% t.-t ?unction i% ?-%t+' -n< .-% -<
•

U%+ HFuGGyLoo3u#!A<<+%% i? you -+ *oo3in& u# n-"+% -n< -<<+%%+% t.-t ?unction .-% -< o<+ conH+ntion% u%+< in Biti%. -<<+%%+%

Y+%' you +-< t.-t co+ct*y you .-H+ - )+%t>"-tc. *oo3u#' vFuzzyLookup_Address() ?o *i%t% o? n-"+% -n< -<<+%%+% 8t cou*< tunin& $8 /ou*
6.+ ?unction% /.ic. /i** )+ o? "o%t int++%t to ot.+ #o&-""+% -+ #o)-)*y  MatchWord()' t.+ %i"#*+ %tin&>co"#-i%on ?unction /.ic. +tun% - #+c+nt-&+ %co+ )-%+< on L+H+n%.t+in +
V?uGGyLoo3u#$(

•

VFuGGyLoo3u#!P.-%+

•

VFuGGyLoo3u#!A<<+%%

•

5-tc.P.-%+

•

5-tc.:o<

•

L+H+n%.t+in

•

No"-*i%+A<<+%%

•

Sti#C.-%

•

Su)%titut+

8n -not.+ #o%t 8 .-H+ co<+< u# -n -*t+n-tiH+ -##o-c. to t.+ L+H+n%.t+in +
O#tion E#*icit Pu)*ic Function VFuGGyLoo3u#$Loo3u#!V-*u+ A% Stin&' 6-)*+!A-y A% V-i-nt' O#tion-* Co*!8n<+!Nu" A% 8nt+&+  1(  Fin< t.+ )+%t "-tc. ?o - &iH+n %tin& in co*u"n 1 o? -n --y o? <-t- o)t-in+< ?o" -n Ec+* -n&+  6.i% i% ?unction-**y %i"i*- to VLoo3u#' )ut it +tun% t.+ )+%t "-tc.' not t.+ ?i%t +-ct "-tc.  6.i% ?unction i% not c-%+>%+n%itiH+  8? you <-t- Iu-*ity i% #oo' you -+ -"-tc.in& %co+ -n<  o<+ conH+ntion% u%+< in Biti%. -<<+%%+%  68S CODE 8S 8N 6E PUBL8C DO5A8N A##*ic-tionVo*-ti*+ F-*%+ Di" <)*B+%t5-tc. A% Dou)*+ Di" Di" Di" Di" Di"

iRo/B+%t <)*5-tc. iRo/ %t6+%t %t8n#ut

Di" iSt-tCo* Di" iEn
A% A% A% A% A%

8nt+&+ Dou)*+ 8nt+&+ Stin& Stin&

A% 8nt+&+ A% 8nt+&+ A% 8nt+&+

8? 8nSt$6y#+N-"+$6-)*+!A-y(' $( @ 8nSt$1' 6y#+N-"+$6-)*+!A-y(' R-n&+' H)6+tCo"#-+( M 1 6.+n 6-)*+!A-y i% not -n --y VFuGGyLoo3u#  =VALUE Eit Function En< 8?

8? 8nSt$1' 6y#+N-"+$6-)*+!A-y(' R-n&+' H)6+tCo"#-+(  0 6.+n 6-)*+!A-y  6-)*+!A-yV-*u+ En< 8?  8? you &+t - %u)%ci#t>out>o?>)oun<% +o .++' you+ u%in& - H+cto in%t+-<  o? t.+ 2> iSt-tCo*

Co*!8n<+!Nu"  Co*!8n<+!Nu" > iO??%+t 8? Co*!8n<+!Nu"  iEno?>)oun<% VFuGGyLoo3u#  =VALUE Eit Function En< 8?

%t8n#ut  UC-%+$Loo3u#!V-*u+( iRo/B+%t  >1 <)*B+%t5-tc.  0 Fo iRo/  LBoun<$6-)*+!A-y' 1( 6o UBoun<$6-)*+!A-y' 1( %t6+%t   %t6+%t  6-)*+!A-y$iRo/' iSt-tCo*( <)*5-tc.  0 <)*5-tc.  5-tc.:o<$%t8n#ut' %t6+%t( 8? <)*5-tc.  1 6.+n  B-i* out on ?in
8? iRo/B+%t  >1 6.+n VFuGGyLoo3u#  =NO 5A6C Eit Function En< 8?

VFuGGyLoo3u#  6-)*+!A-y$iRo/B+%t' Co*!8n<+!Nu"(

En< Function

Pu)*ic Function 5-tc.:o<$ByV-* %t1 A% Stin&' ByV-* %t2 A% Stin&' O#tion-* Co"#-+ A% V)Co"#-+5+t.o<  H)6+tCo"#-+( A% Dou)*+  R+tun% - #+c+nt-&+ +%ti"-t+ o? .o/ c*o%+*y /o< 1 "-tc.+% /o< 2  E
8? %t1  %t2 6.+n 5-tc.:o<  1 Eit Function En< 8?

 

8? L+n$%t1( "-L+n  "inL+n  E*%+ "-L+n  "inL+n  En< 8?

 L+n$%t2( 6.+n L+n$%t1( L+n$%t2( L+n$%t2( L+n$%t1(

5-tc.:o<  0 5-tc.:o<  L+H+n%.t+in$%t1' %t2(

 

8? 5-tc.:o<  "inL+n 6.+n 5-tc.:o<  0 E*%+ 5-tc.:o<  $"-L+n > 5-tc.:o<(  "-L+n En< 8?

En< Function

Pu)*ic Function VFuGGyLoo3u#!P.-%+$Loo3u#!P.-%+ A% Stin&' 6-)*+!A-y A% V-i-nt' O#tion-* Co*!8n<+!Nu" A% 8nt+&+  1(

 Fin< t.+ )+%t "-tc. ?o - &iH+n #.-%+ in co*u"n 1 o? -n --y o? <-t- o)t-in+< ?o" -n Ec+* -n&+  U%+ t.i% ?unction to "-tc. - %+nt+nc+ $- %+Iu+nc+ o? /o<% %+#--t+< )y %#-c+%( R+tun% - %co+  )-%+< on "-tc.in& /o< o<+ /+i&.t+< )y t.+ %tin&>"-tc.in& %co+ o? +-c. in%+n%itiH+  8? you <-t- Iu-*ity i% #oo' you -+ -"-tc.in& %co+ con%i<+  o<+ conH+ntion% u%+< in Biti%. -<<+%%+%

 68S CODE 8S 8N 6E PUBL8C DO5A8N A##*ic-tionVo*-ti*+ F-*%+ Di" <)*B+%t5-tc. A% Dou)*+ Di" Di" Di" Di" Di"

iRo/B+%t <)*5-tc. iRo/ %t6+%t %t8n#ut

Di" iSt-tCo* Di" iEn
A% A% A% A% A%

8nt+&+ Dou)*+ 8nt+&+ Stin& Stin&

A% 8nt+&+ A% 8nt+&+ A% 8nt+&+

8? 8nSt$6y#+N-"+$6-)*+!A-y(' $( @ 8nSt$1' 6y#+N-"+$6-)*+!A-y(' R-n&+' H)6+tCo"#-+( M 1 6.+n 6-)*+!A-y i% not -n --y VFuGGyLoo3u#!P.-%+  =VALUE Eit Function En< 8? 8? 8nSt$1' 6y#+N-"+$6-)*+!A-y(' R-n&+' H)6+tCo"#-+(  0 6.+n 6-)*+!A-y  6-)*+!A-yV-*u+ En< 8?  8? you &+t - %u)%ci#t>out>o?>)oun<% +o .++' you+ u%in& - H+cto in%t+-<  o? t.+ 2>
iSt-tCo*  LBoun<$6-)*+!A-y' 2( iEn iSt-tCo*

Co*!8n<+!Nu"  Co*!8n<+!Nu" > iO??%+t 8? Co*!8n<+!Nu"  iEno?>)oun<% VFuGGyLoo3u#!P.-%+  =VALUE Eit Function En< 8?

%t8n#ut  UC-%+$Loo3u#!P.-%+( iRo/B+%t  >1 <)*B+%t5-tc.  0 Fo iRo/  LBoun<$6-)*+!A-y' 1( 6o UBoun<$6-)*+!A-y' 1( %t6+%t   %t6+%t  6-)*+!A-y$iRo/' iSt-tCo*( <)*5-tc.  0 <)*5-tc.  5-tc.P.-%+$%t8n#ut' %t6+%t(  Con%i<+ co
8? iRo/B+%t  >1 6.+n VFuGGyLoo3u#!P.-%+  =NO 5A6C Eit Function En< 8?

VFuGGyLoo3u#!P.-%+  6-)*+!A-y$iRo/B+%t' Co*!8n<+!Nu"(

En< Function

Pu)*ic Function VFuGGyLoo3u#!A<<+%%$Loo3u#!A<<+%% A% Stin&' 6-)*+!A-y A% V-i-nt' O#tion-* Co*!8n<+!Nu" A% 8nt+&+  1(  Fin< t.+ )+%t "-tc. ?o - &iH+n #o%t-* -<<+%% in co*u"n 1 o? - t-)*+ o? -<<+%%+% o)t-in+< ?o" -n Ec+* -n&+  6.i% i% ?unction-**y %i"i*- to VLoo3u#' )ut it +tun% t.+ )+%t "-tc.' not t.+ ?i%t +-ct "-tc.  6.i% ?unction i% not c-%+>%+n%itiH+ -n< i&no+% co""on -))+Hi-tion% +& St ?o St++t  8? you <-t- Iu-*ity i% #oo' you -+ -"-tc.in& %co+ con%i<+ 
 68S CODE 8S 8N 6E PUBL8C DO5A8N A##*ic-tionVo*-ti*+ F-*%+ Di" <)*B+%t5-tc. A% Dou)*+ Di" Di" Di" Di" Di"

iRo/B+%t <)*5-tc. iRo/ %t6+%t %t8n#ut

Di" iSt-tCo* Di" iEn
A% A% A% A% A%

8nt+&+ Dou)*+ 8nt+&+ Stin& Stin&

A% 8nt+&+ A% 8nt+&+ A% 8nt+&+

8? 8nSt$6y#+N-"+$6-)*+!A-y(' $( @ 8nSt$1' 6y#+N-"+$6-)*+!A-y(' R-n&+' H)6+tCo"#-+( M 1 6.+n 6-)*+!A-y i% not -n --y VFuGGyLoo3u#!A<<+%%  =VALUE Eit Function En< 8? 8? 8nSt$1' 6y#+N-"+$6-)*+!A-y(' R-n&+' H)6+tCo"#-+(  0 6.+n 6-)*+!A-y  6-)*+!A-yV-*u+ En< 8?

 8? you &+t - %u)%ci#t>out>o?>)oun<% +o .++' you+ u%in& - H+cto in%t+-<  o? t.+ 2> iSt-tCo*

Co*!8n<+!Nu"  Co*!8n<+!Nu" > iO??%+t 8? Co*!8n<+!Nu"  iEno?>)oun<% VFuGGyLoo3u#!A<<+%%  =VALUE Eit Function En< 8?

%t8n#ut  Loo3u#!A<<+%% %t8n#ut  No"-*i%+A<<+%%$%t8n#ut(

8? %t8n#ut   6.+n   Out>o?>)oun<% VFuGGyLoo3u#!A<<+%%  =CANNO6 READ ADDRESS Eit Function En< 8?

iRo/B+%t  >1 <)*B+%t5-tc.  0 Fo iRo/  LBoun<$6-)*+!A-y' 1( 6o UBoun<$6-)*+!A-y' 1( %t6+%t   %t6+%t  6-)*+!A-y$iRo/' iSt-tCo*( %t6+%t  No"-*i%+A<<+%%$%t6+%t( 8? %t6+%t M  6.+n <)*5-tc.  0 <)*5-tc.  5-tc.P.-%+$%t8n#ut' %t6+%t(  Con%i<+ co
En< 8?  %t6+%t M  N+t iRo/

8? iRo/B+%t  >1 6.+n VFuGGyLoo3u#!A<<+%%  =NO 5A6C Eit Function En< 8?

VFuGGyLoo3u#!A<<+%%  6-)*+!A-y$iRo/B+%t' Co*!8n<+!Nu"(

En< Function

Pu)*ic Function 5-tc.P.-%+$ByV-* P.-%+1 A% Stin&' ByV-* P.-%+2 A% Stin&' O#tion-* Co"#-+ A% V)Co"#-+5+t.o<  H)6+tCo"#-+( A% Dou)*+  Function to co"#-+ t/o %+nt+nc+% A H+%ion o? t.i% /i** )+ +*+-%+< to c-t+ ?o t.+  %#+ci?ic n++<% o? "-tc.in& -<<+%%+%' /.++ /+ c-n "-3+ %o"+ -%%u"#tion% -)out co""on  /o<>%u)%titution% -n< -))+Hi-tion%  68S CODE 8S 8N 6E PUBL8C DO5A8N

 6.i% ?unction con%i%t% o? %i #oc+%%+%  1 B+-3 out t.+ #.-%+% into --y% o? /o<% u%in& t.+ %#-c+ c.--ct+ -% t.+ <+*i"it+  2 Po#u*-t+ - &i< o? /o<>"-tc.in& %co+% ?o +-c. /o< in P.-%+ 1 -&-in%t P.-%+ 2   Fo +-c. /o< in P.-%+ 1' i<+nti?y t.+ )+%t "-tc. ?o" t.+ /o<% in P.-%+ 2  J R+%o*H+ co**i%ion% > t/o o "o+ /o<% in #.-%+ 1 "-tc.in& t.+ %-"+ /o< in #.-%+ 2   Co"#-+ t.+ -ctu-* %+Iu+nc+ o? /o<% in P1 /it. t.+ #o%ition% o? t.+ "-tc.+< /o<% in P2   :+i&.t t.i% co"#-i%on )y t.+ <+&++ o? "-tc.in& "+-%u+< -t t.+ *+H+* o? in
Di" -1$( A% Stin& Di" -2$( A% Stin&

 P.-%+ 1' )o3+n out into in
Di" -Sco+%$( A% Dou)*+ -&-in%t +-c. /o< in #2

 -n --y o? #+c+nt-&+ "-tc.+% o? +-c. /o< in #1

 6.+%+ t/o H+cto% -+ + "-tc.in& /o< in #2 Di" -S+Iu+nc+$( A% Dou)*+  Fo +-c. /o< in #1' - %co+ ?o it% conco<-nc+ /it. - con%tuct+< %+Iu+nc+ o? "-tc.in& /o<% in P2

Di" n A% Dou)*+ ?*o-tin&>#oint

 %.ou*< )+ -n int+&+' )ut it /i** )+ u%+< in 
Di" %1 A% Stin& Di" %2 A% Stin&

Di" i A% 8nt+&+ Di" 9 A% 8nt+&+ Di" 3 A% 8nt+&+ Di" iO??%+t A% 8nt+&+ Di" iS.i?t A% 8nt+&+ Di" iD+*+t+ A% 8nt+&+ Di" iPo% A% 8nt+&+ Di" 9Po% A% 8nt+&+ Di" 3Po% A% 8nt+&+ Di" i6ot-*L+n A% 8nt+&+ Di"
P.-%+2  Sti#C.-%$P.-%+2'  ( S#*itS#-c+2 -2  S#*it$P.-%+2'  ( R+Di" -Sco+%$LBoun<$-1( 6o UBoun<$-1(' LBoun<$-2( 6o UBoun<$-2(( R+Di" -Po%ition%$LBoun<$-1( 6o UBoun<$-1(( R+Di" -S+Iu+nc+$LBoun<$-1( 6o UBoun<$-1((

    

6+%t ?o <+*+t+< %#-c+% 6.i% i% - *ot o? /o3' )ut - "i%%in& %#-c+ i% co""on +o -n< t.+ +??+ct% -+ out o? -** #o#otion to t.+ %iG+ o? t.+ +o %o "uc. %o t.-t 8" #+#-+< to i%3 t.+ occ-%ion-* ?-*%+ -*-" 8t "-y +H+n )+ /ot. +#+-tin& t.+%+ t/o *oo#% u%in& ?uGGy>"-tc.in& /it. L+H+n%.t+in %co+% -t.+ t.-n t.+ %i"#*+ %tin&>co"#-i%on% %.o/n )+*o/

Fo i  LBoun<$-1( 6o UBoun<$-1( > 1 8? -1$i( M  An< -1$i @ 1( M  6.+n %1  -1$i( Q -1$i @ 1( Fo 9  LBoun<$-2( 6o UBoun<$-2( 8? UC-%+$-2$9((  UC-%+$%1( 6.+n P.-%+2  Su)%titut+$P.-%+2' -2$9(' -1$i( Q   Q -1$i @ 1(' 1' Co"#-+( o6o S#*itS#-c+2 En< 8? N+t 9 En< 8?  -$i(   O -$i @ 1(   6.+n N+t i Fo 9  LBoun<$-2( 6o UBoun<$-2( > 1 8? -2$9( M  An< -2$9 @ 1( M  6.+n %2  -2$9( Q -2$9 @ 1( Fo i  LBoun<$-1( 6o UBoun<$-1( 8? UC-%+$-1$i((  UC-%+$%2( 6.+n P.-%+1  Su)%titut+$P.-%+1' -1$i(' -2$9( Q   Q -2$9 @ 1(' 1' Co"#-+( o6o S#*itS#-c+1 En< 8? N+t i En< 8? N+t 9

 8niti-*i%+ t.+ #o%ition% --y /it. - n+&-tiH+ H-*u+ <+notin& not ?oun<

Fo i  LBoun<$-1( 6o UBoun<$-1( -Po%ition%$i(  >1 i6ot-*L+n  i6ot-*L+n @ L+n$-1$i(( N+t i  Fo +-c. /o< in P.-%+ 1' i<+nti?y t.+ c*o%+%t "-tc.in& in P.-%+ 2 -n< +co< it% #o%ition Fo i  LBoun<$-1( 6o UBoun<$-1( %1  -1$i( 1 Fo 9  LBoun<$-2( 6o UBoun<$-2( %2  -2$9( t/o o "o+ /o<% in P1 t.-t .-H+ )+%t "-tc. %co+% on t.+ %-"+ /o< in #2  8n t.+oy t.i% cou*< )+ 1

?in< t.+ %+con<>)+%t %co+ ?o <1 1 Fo 3  LBoun<$-Sco+%' 2( 6o UBoun<$-Sco+%' 2( 1 8? -Po%ition%$3(  3Po% 6.+n +%t-t t.+ *oo# -t t.+ co**i
E*%+  1 ?in< t.+ %+con<>)+%t %co+ ?o <2 T-?t+T t.+ cu+nt #o%ition 1 Fo 3  9 @ 1 6o UBoun<$-2(
N+t i

 Con%tuctin& - %+Iu+nc+>"-tc.in& %co+

 8? /+ /++ %coin& 9u")*+< %+nt+nc+% o? un-*t++< /o<%' /+< u%+ -n +#-%% -*&oit." /it. - ?o/-< )i-%' t.-t +#+ct% t.+ /o< %+Iu+nc+ to  +%ync.oni%+ -?t+ +-c. out>o?>%+Iu+nc+ /o< 8t% Iuic3' -n< t.+ )i-% i% H-*i< > /o<>o<+  i% not n+ut-* in +-*>*i?+ +-"#*+%' -n< t.+ .+-Hy #+n-*ty ?o /o< t-n%#o%ition% +?*+ct%  "y )+*i+? t.-t t.i% i% - "o+ %i&ni?ic-nt +*-n&u-&+ #oc+%%in& t.-t i%  out>o?>%co#+ ?o t.i% -##*ic-tion -n< ?- too -")itiou% ?o - %+*?>cont-in+< ?unction in VBA  :o3+< +-"#*+  Co"#-+ t/o P.-%+%  ABC DEF 8 KL 5NO PR S6U V:X'

ABC DEF KL S6U 5NO PR V:X

 V-i-)*+ -Po%ition% +co<% t.+ #*-c+"+nt o? +-c. /o< in #.-%+ 1 in #.-%+ 2  P.-%+ 1 ABC DEF 8 KL 5NO PR S6U V:X  E#+ct+< #o%ition% 0 1 2  J   7  Actu-* #o%ition in #2 0 1 >1 2 J     6.+ H-i-)*+ -S+Iu+nc+ /i** c-#tu+ t.+ %co+%  Run t.+ %+Iu+nc+>%coin& *oo#  ABC  DEF  8  KL  5NO  PR  S6U $7W(  V:X $7W(2

+#+ct+< +#+ct+< +#+ct+< +#+ct+< +#+ct+< +#+ct+< +#+ct+<

in in in in in in in

#o%ition #o%ition #o%ition #o%ition #o%ition #o%ition #o%ition

0 1 2 >1 J>1  

+#+ct+< in #o%ition 7>

?oun< in ?oun< in DELE68ON ?oun< in ?oun< in ?oun< in ?oun< in

0 1 2 J  

?oun< in 

Sco+ Sco+ T ?-"+ %.i?t >1 T Sco+ Sco+ T ?-"+ %.i?t @1 T Sco+ Sco+ T ?-"+ %.i?t > T Sco+

1W 1W N8L 1W 1W T 7W 1W 1W T

T ?-"+ %.i?t @2 T Sco+ 1W T

 Eo?>%+Iu+nc+ #+n-*ty o? 7W /i** )+ -##*i+< %+H+n ti"+%  o/+H+' /+ "-tc.+< /o<% in +-* *i?+' %o /+ c-nnot -##*y  t.+%+ #+n-*ti+% -t t.+ *+H+* o? t.+ +nti+ #.-%+ /+ -##*y t.+" -t t.+ *+H+* o? t.+  in
 6.+ +c+#tion i% <+*+t+< /o<% /+ cou*< con%i<+ t.+ /o< "-tc. /+i&.tin& o? G+o  to )+ %u??ici+nt #+n-*ty )ut - "o+ con%i%t+nt +%u*t i% o)t-in+< )y -##*yin& #+n-*ty  to t.+ +nti+ #.-%+

 S-nity c.+c3 un t.+ ?unction in +H+%+' t+%tin& P.-%+ 2 -&-in%t #.-%+ 1  P.-%+ 2 ABC DEF KL S6U 5NO PR V:X  E#+ct+< #o%ition% 0 1 2  J    Actu-* #o%ition in #1 0 1   J  7  ABC  DEF  KL  S6U $7W(2  5NO $7W(  PR  V:X

+#+ct+< +#+ct+< +#+ct+< +#+ct+<

in in in in

#o%ition #o%ition #o%ition #o%ition

0 1 2 @1

?oun< ?oun< ?oun< ?oun<

in in in in

0 1  

Sco+ Sco+ T ?-"+ %.i?t @1 T Sco+ T ?-"+ %.i?t @2 T Sco+

1W 1W 1W T 7W 1W T

+#+ct+< in #o%ition J@

?oun< in J

T ?-"+ %.i?t > T Sco+ 1W T

+#+ct+< in #o%ition  +#+ct+< in #o%ition 

?oun< in  ?oun< in 7

Sco+ 1W T ?-"+ %.i?t >1 T Sco+ 1W T 7W

 Eo?>%+Iu+nc+ #+n-*ty o? 7W /i** )+ -##*i+< %+H+n ti"+%  But /-%nt t.++ -n in%+tion' too P.-%+ 1 .-% -n +t- /o< t.-t i%nt in P.-%+ 24  Not+ t.-t ou c.oic+ o? <+no"in-to $W' t.+ *on&+ o? t.+ t/o /o
8? UBoun<$-1(  UBoun<$-2( 6.+n n  UBoun<$-1( @ 1 E*%+ n  UBoun<$-2( @ 1 En< 8? $1  n( iS.i?t  0  S+Iu+nc+ o?>#*-c+ /o<% iO??%+t  0  Runnin& tot-* o? t.i% %.i?t H-i-)*+ iD+*+t+  0  Count t.+ nu")+ o? <+*+tion% Fo i  LBoun<$-Po%ition%( 6o UBoun<$-Po%ition%(

%1  -1$i( iPo%  -Po%ition%$i( iS.i?t  iPo% > i > iO??%+t

S+*+ct C-%+ iPo% C-%+ 8% M 0 DELE68ON no "-tc.in& /o< /-% ?oun< in S2 iS.i?t  >1 -S+Iu+nc+$i(  0 iD+*+t+  iD+*+t+ @ 1 C-%+ 8%  i @ iO??%+t  "-tc.+< /o< i% in t.+ +#+ct+< #o%ition iS.i?t  0 -S+Iu+nc+$i(  1  n C-%+ E*%+ -S+Iu+nc+$i(  $
Fo i  LBoun<$-Po%ition%( 6o UBoun<$-Po%ition%( 1 6.+n t.i% i%nt -% -)it-y -% it "i&.t %++" it i% +IuiH-*+nt to t.+  +??+ct o? -n in%+tion' /.ic. -ct% )y inc+-%in& t.+ <+no"in-to L+n$-1$i((  i6ot-*L+n  n En< 8? 5-tc.P.-%+  5-tc.P.-%+ @
EitFunction E-%+ -Sco+% E-%+ -S+Iu+nc+ E-%+ -1

E-%+ -2 En< Function PiH-t+ Function 5ini"u"$ByV-* - A% 8nt+&+' ! ByV-* ) A% 8nt+&+' ! ByV-* c A% 8nt+&+( A% 8nt+&+ Di" "in A% 8nt+&+ "in  8? ) M "in 6.+n "in  ) En< 8? 8? c M "in 6.+n "in  c En< 8? 5ini"u"  "in En< Function

PiH-t+ Function L+H+n%.t+in$ByV-* %1 A% Stin&' ByV-* %2 A% Stin&( A% 8nt+&+  L+H+n%.t+in Di%t-nc+

> +
 68S CODE 8S 8N 6E PUBL8C DO5A8N

Di" Di" Di" Di" Di" Di" Di" Di"

-$( A% 8nt+&+ n A% 8nt+&+ " A% 8nt+&+ i A% 8nt+&+ 9 A% 8nt+&+ %1!i A% Stin& %2!9 A% Stin& co%t A% 8nt+&+

       

Scoin& "-ti *+n&t. o? %1 *+n&t. o? %2 it+-t+% t.ou&. it+-t+% t.ou&. it. c.--ct+ o? 9t. c.--ct+ o? co%t

n  L+n$%1( "  L+n$%2( 8? n  0 6.+n L+H+n%.t+in  " Eit Function En< 8? 8? "  0 6.+n L+H+n%.t+in  n Eit Function En< 8? R+Di" -$0 6o n' 0 6o "( A% 8nt+&+

%1 %2 %1 %2

Fo i  0 6o n -$i' 0(  i N+t i Fo 9  0 6o " -$0' 9(  9 N+t 9

Fo i  1 6o n %1!i  5i<;$%1' i' 1( Fo 9  1 6o " %2!9  5i<;$%2' 9' 1( 8? %1!i  %2!9 6.+n co%t  0 E*%+ co%t  1 En< 8?

 

-$i' 9(  5ini"u"$-$i > 1' 9( @ 1' -$i' 9 > 1( @ 1' -$i > 1' 9 > 1( @ co%t( N+t 9 N+t i  St+# 7 L+H+n%.t+in  -$n' "( EitSu) E-%+ - En< Function

Pu)*ic Function No"-*i%+A<<+%%$ByV-* %tA<<+%% A% Stin&( A% Stin&  6.i% ?unction i% int+n<+< to +"oH+ o %t-n<-co"#-i%on -*&oit."% in *i%t% o? n-"+% -n< -<<+%%+%  D+H+*o#+% in ot.+ counti+% %.ou*< +Hi+/ t.+ /o< *i%t u%+< .++'  -% conH+ntion% #o)-)*y
   

Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%'

''  ( '  ( >'  ( H)CL?'  (

%tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%%

           

Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%'

           

%tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%%

     

Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%'

Q' AND(  L8586ED '  L6D (  CO5PANY '  CO (  CORPORA68ON '  CORP (  6A '  6A (  6RAD8N AS '  6A (

    

BLVD '  BOULEVARD ( BVD '  BOULEVARD ( AV '  AVENUE ( AVE '  AVENUE ( RD '  ROAD ( :Y '  :AY ( ES6 '  ES6A6E ( PL '  PLACE ( PK '  PARK ( SE '  OUSE ( 0 '  OUSE ( DNS '  ARDENS (

Co""on #+%on-* tit*+% t.+%+ -+ o?t+n -##*i+< incon%i%t+nt*y o o"itt+<' -n< "u%t t.++?o+ )+ +"oH+< S#+ci?ic -##*ic-tion% "-y +Iui+ -< "i*it-y -n3' -c-<+"ic tit*+% -n< <+&++%' cout+%y tit*+% o? t.+ -i%toc-cy' 3ni&.t.oo<% -n< .onou% $#-ticu*-*y ?o *i%t% o? ciHi* %+H-nt%(

%tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%% %tA<<+%%

                 

Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%' Su)%titut+$%tA<<+%%'

                 

ES '  ( 5R '  ( 5RS '  ( 58SS '  ( 5S '  ( 5ESSRS '  ( S8R '  ( OF '  ( DR '  ( OR '  ( 8N '  ( 6E '  ( REVEREND '  REV ( REVEREN6 '  REV ( ONOURABLE '  ON ( BROS '  BRO6ERS ( ASSOC '  ASSOC8A68ON ( ASSN '  ASSOC8A68ON (

 St-n<-
Do :.i*+ 8nSt$%tA<<+%%'  (  0 %tA<<+%%  Su)%titut+$%tA<<+%%'  Loo#

'  (

%tA<<+%%  6i"$%tA<<+%%( No"-*i%+A<<+%%  %tA<<+%% En< Function

Pu)*ic Function Sti#C.-%$"yStin& A% Stin&' P--"A-y Ec+#tion%$(( A% Stin&  Sti# out -** non>-*#.-nu"+ic c.--ct+% ?o" - %tin& in - %in&*+ #-%%  Ec+#tion% #--"+t+% -**o/ you to +t-in %#+ci?ic c.--ct+% $+& %#-c+%(  68S CODE 8S 8N 6E PUBL8C DO5A8N A##*ic-tionVo*-ti*+ F-*%+ Di" Di" Di" Di" Di" Di" Di"

i A% 8nt+&+ iL+n A% 8nt+&+ c.A A% Stin& T 1 intA A% 8nt+&+ 9 A% 8nt+&+ iSt-t A% 8nt+&+ iEn< A% 8nt+&+

8? Not 8%E"#ty$Ec+#tion%$(( 6.+n iSt-t  LBoun<$Ec+#tion%( iEn<  UBoun<$Ec+#tion%( En< 8? iL+n  L+n$"yStin&( Fo i  1 6o iL+n c.A  5i<$"yStin&' i' 1( intA  A%c$c.A( S+*+ct C-%+ intA C-%+ JW 6o 7'  6o 0' 7 6o 122 Sti#C.-%  Sti#C.-% Q c.A C-%+ E*%+ 8? Not 8%E"#ty$Ec+#tion%$(( 6.+n Fo 9  iSt-t 6o iEn< 8? c.A  Ec+#tion%$9( 6.+n Sti#C.-%  Sti#C.-% Q c.A Eit Fo  9 En< 8? N+t 9 En< 8? En< S+*+ct N+t i

En< Function

PiH-t+ Function Su)%titut+$ByV-* 6+t A% Stin&' ! ByV-* O*
R+#*-c+ -** in%t-nc+% $o t.+ nt. in%t-nc+ ( o? O*< t+t /it. N+/  Un*i3+ VB5i<; t.i% "+t.o< i% not %+n%itiH+ to *+n&t. -n< c-n +#*-c+ ALL in%t-nc+%  6.i% i% not +#o%+< -% - Pu)*ic ?unction )+c-u%+ t.++ i% -n Ec+* :o3%.++t ?unction c-**+< Su)%titut+$(  68S CODE 8S 8N 6E PUBL8C DO5A8N Di" Di" Di" Di" Di"

iSt-t A% 8nt+&+ iEn< A% 8nt+&+ iL+n A% 8nt+&+ i8n%t-nc+ A% 8nt+&+ %tOut A% Stin&

iL+n  L+n$O*
%tOut   Do Unti* iEn<  0 %tOut  %tOut Q 5i<;$6+t' iSt-t' iEn< > iSt-t( i8n%t-nc+  i8n%t-nc+ @ 1

 

8? 8n%t-nc+  0 O 8n%t-nc+  i8n%t-nc+ 6.+n %tOut  %tOut Q N+/!6+t E*%+ %tOut  %tOut Q 5i<;$6+t' iEn<' L+n$O*
iSt-t  iEn< @ iL+n iEn<  8nSt$iSt-t' 6+t' O* iEn<( Su)%titut+  %tOut En< Function

Pu)*ic Function Cont-in%$ByV-* 5-inStin& A% Stin&' P--"A-y S++3Stin&$(( A% Boo*+-n  A H+%ion o? 8n%t$( t.-t +tun% 6RUE i? -ny %++3 %tin& i% - %u)%tin& o? t.+ "-in %tin&  68S CODE 8S 8N 6E PUBL8C DO5A8N A##*ic-tionVo*-ti*+ F-*%+ Di" i A% 8nt+&+ Di" 9 A% 8nt+&+ Di" -X A% V-i-nt Di" %t6+%t A% Stin& Cont-in%  F-*%+ 8? 5-inStin&   6.+n Eit Function En< 8?

Fo i  LBoun<$S++3Stin&( 6o UBoun<$S++3Stin&( 8? 8%A-y$S++3Stin&$i(( 6.+n -X  S++3Stin&$i( Fo 9  LBoun<$-X( 6o UBoun<$-X( %t6+%t   %t6+%t  6i"$-X$9(( 8? L+n$%t6+%t( M L+n$5-inStin&( An< L+n$%t6+%t(  0 6.+n 8? 8nSt$1' 5-inStin&' %t6+%t' H)6+tCo"#-+(  0 6.+n Cont-in%  6u+ Eit Function En< 8? En< 8? N+t 9

E-%+ -X  

E*%+ %t6+%t   %t6+%t  6i"$S++3Stin&$i(( 8? L+n$%t6+%t(  0 6.+n 8? L+n$%t6+%t( M L+n$5-inStin&( An< L+n$%t6+%t(  0 6.+n 8? 8nSt$1' 5-inStin&' %t6+%t' H)6+tCo"#-+(  0 6.+n Cont-in%  6u+ Eit Function En< 8? En< 8? En< 8? En< 8?

N+t i En< Function 6.++% - #-ticu*- #oint -)out t.+ P.-%+>"-tc.in& ?unction t.-t cou*< #o)-)*y )+ i"#oH+<  Fo +-c. /o< in P.-%+ 1' i<+nti?y t.+ c*o%+%t "-tc.in& in P.-%+ 2 -n< +co< it% #o%ition

Acc+%% c-n inco#o-t+ &*o)-**y><+c*-+< VBA ?unction% in Iu+i+%' %o ty t.i% out

SELEC6 6OP 

     

Buc3in&.-" P-*-c+' LONDON S:1 AS S++3A<<+%%' 5-tc.P.-%+$ No"-*i%+A<<+%%$Buc3in&.-" P-*-c+' LONDON S:1(' No"-*i%+A<<+%%$t)*A<<+%%]Fu**A<<+%%]( ( AS 5-tc.Sco+' t)*A<<+%%T

FRO5 t)*A<<+%% ORDER BY   5-tc.P.-%+$ No"-*i%+A<<+%%$Buc3in&.-" P-*-c+' LONDON S:1('   No"-*i%+A<<+%%$t)*A<<+%%]Fu**A<<+%%]( ( DESC

Function SOUNDEX$Sun-"+ A% Stin&( A% Stin&  6.+ Cu%to" Function SOUNDEX u%+< in t.i% "-co /-% D+H+*o#+< )y Ric.-<  Y-nco  6.i% ?unction ?o**o/% t.+ Soun<+ u*+% &iH+n -t  .tt#.o"+ut-.>int+n+t3in%+-c.Soun<+.t"*  6.+ VB A##*ic-tion Soun<+!Co<+% By o+ :-% Q o.n :-*3+n)-c. Di" R+%u*t A% Stin&' c A% Stin& T 1 Di" Loc-tion A% 8nt+&+ Sun-"+  UC-%+$Sun-"+(  Fi%t c.--ct+ "u%t )+ - *+tt+ 8? A%c$L+?t$Sun-"+' 1(( M  O A%c$L+?t$Sun-"+' 1((  0 6.+n SOUNDEX   Eit Function E*%+  St i% conH+t+< to S-int 8? L+?t$Sun-"+' (  S6 6.+n Sun-"+  SA8N6 Q 5i<$Sun-"+' J( En< 8?  ConH+t to Soun<+ *+tt+% to t.+i -##o#i-t+ *+n&t. %tin& R+%u*t  L+?t$Sun-"+' 1( Fo Loc-tion  2 6o L+n$Sun-"+( R+%u*t  R+%u*t Q C-t+&oy$5i<$Sun-"+' Loc-tion' 1(( N+t Loc-tion  R+"oH+
R+%u*t  L+?t$R+%u*t' Loc-tion > 1( Q 5i<$R+%u*t' Loc-tion @ 1( En< 8? N+t  6i" o #-< /it. G+o+% -% n+c+%%-y S+*+ct C-%+ L+n$R+%u*t( C-%+ J SOUNDEX  R+%u*t C-%+ 8% M J SOUNDEX  R+%u*t Q Stin&$J > L+n$R+%u*t(' 0( C-%+ 8%  J SOUNDEX  L+?t$R+%u*t' J( En< S+*+ct En< 8? En< Function PiH-t+ Function C-t+&oy$c( A% Stin&  R+tun% - Soun<+ co<+ ?o - *+tt+ S+*+ct C-%+ 6u+ C-%+ c Li3+ AE8OUY] C-t+&oy   C-%+ c Li3+ BPFV] C-t+&oy  1 C-%+ c Li3+ CSKXZ] C-t+&oy  2 C-%+ c Li3+ D6] C-t+&oy   C-%+ c  L C-t+&oy  J C-%+ c Li3+ 5N] C-t+&oy   C-%+ c  R C-t+&oy   C-%+ E*%+ 6.i% inc*u<+%  -n< :' %#-c+%' #unctu-tion' +tc C-t+&oy   En< S+*+ct En< Function