Need to fill in map coordinates for addresses

[B. Meincke]

I have done a little programming in Access but am new to it in Excel. I have
been reading earlier posts for a solution to my situation and am not at all
sure which function would suit it best. Here it is:

I have a workbook with two sheets. The first sheet is a list of addresses a
real estate agent will present buyers looking to visit open houses. As well
as the address fields for each record (street number and street and city) it
also includes a field for map lookup coordinates, so a record might look like
this:

City No. Street Area Map
Coordinates
Ourville 123 Rue de Us 321 C10
Youville 81-765 Them St 246 B12

The second sheet contains a list of all streets in the area sorted by city
and their corresponding coordinates. This file is sent to us from the
realestate board.

What we would like to be able to do is to enter the street addresses for the
week's open houses and have the spreadsheet automatically enter the area and
coordinate data.

There are really two problems that I can see:
1. Some address numbers include a unit numbers divided from the address by
a hyphen
2. The coordinate sheet has multiple entries for long streets and lists
house numbers in ranges...for example:

Street Area Coordinates
Ru de Us 100-450 321 A10
Ru de Us 451-900 322 A11
Ru de Us 901-1275 323 A11

I have looked at lookups and I don't think that will work. I have read about
match arrays but am not sure I completely understand what that means or
whether or not it is what I need to do in my case.

If I can make this clearer by providing any further information, I'd be
happy to. Thanks in advance for any suggestions or advice.

 

[Bernie Deitrick]

B.,

First, you need to change your data entry:

City No. Street Area Map
Coordinates
Ourville 123 Rue de Us 321 C10
Youville 81-765 Them St 246 B12

Your street addresses can't have dashes in them - change the number
following the dash into a separate field - leave it blank for those without
the dashes.

City No. Unit Street Area
Map
Coordinates
Ourville 123 Rue de Us 321
C10
Youville 81 765 Them St 246
B12

And on your coordinate sheet, you need to split out the street name and the
numbers. To do that put these formulas into cells D2:G2 (assuming your
three columns are A to C, and that street names are unique to a town, with
street name and number in A, Area in B, and Coords in C)

=MID(A2,FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2,"
",""))),1)+1,200)
=TRIM(SUBSTITUTE(A2,D2,""))
=VALUE(LEFT(D2,FIND("-",D2)-1))
=VALUE(MID(D2,FIND("-",D2)+1,100))

Then you can copy that down to match.

Then, to get your Area and Map Coords, use a formula like

=SUMPRODUCT((B2='Data Sheet name'!$E$2:$E$6000)*(A2>='Data Sheet
name'!$F$2:$F$6000)*(A2<='Data Sheet name'!$G$2:$G$6000)*'Data Sheet
name'!$B$2:$B$6000)
=SUMPRODUCT((B2='Data Sheet name'!$E$2:$E$6000)*(A2>='Data Sheet
name'!$F$2:$F$6000)*(A2<='Data Sheet name'!$G$2:$G$6000)*'Data Sheet
name'!$C$2:$C$6000)

Where A2 is the cell that has the number, B2 has the street name, and the
formulas from above are on a sheet named "Data Sheet name", with area in
column B, and Map Coords in column C.

Again, this assumes that street names are unique...

HTH,
Bernie
MS Excel MVP

 

[Dylan @ UAFC]

Dont get over board
you need to the unqunes of the data
If you are doing the intial data entry
for that will eventally popluate the areas
you want
try to get phone #'s they are the most unique data
value in serries that can sorte ascending to helo with speed

vlookup is the best way to go for simple poplation of cells.
send
the physical address can be unique as well it will make the
system rum a little slower, but if you are not dealing with
more than 10,000 rows that should not be a big deal

ok
take your data that you want to popluate
orginze by the physical address
in col A on sheet 2 and then all the
rest go to right
on sheet one
say col a
is were you would type the phsical address
col b and on is were you want popluation
in b type
=vlookup(a1,sheet2!$a$1:$b$1000,2,FALSE)
this will copy down
same formula in col c
except change the 2,FALSE to 3FALSE and then on down the line
at fist before you type the data the cell intending to be popluated will
say #n/A
if you cannot handle that wright back