Query needed - user enters zip code and chooses radius and matches against another table

[Terrapin69]

Hi,

Not sure if this is the right forum...

I have an Excel sheet with approx. 3500 entries-

I would like to create a query/search...whatever wherein a user could enter a zip code, choose a radius (20, 30, 50...miles) and the results would return all zip codes found within that radius.

something that can be even accomplished w/in Excel?

[Bernie Deitrick]

Download the latitudes and longitudes for all zip codes from here:

https://public.opendatasoft.com/expl...gitude/export/

Then find the latitudes and longitudes for all your 3500 entries.

The use the great circle formula from here:

http://www.cpearson.com/excel/LatLong.aspx

and calculate all the distances for your 3500 entries based on the select zip code, and then filter based on the selected radius.

[Terrapin69]

wow! I will try and thanks - I'll report back

[Terrapin69]

First thing I need to do, in Google Sheets apparently, is to attach zip codes to the entries missing them. I have about 3000 and, in Sheets, and obviously running into the service invoked too many times in one day error - How can I work around this for free or paid?

Here's my code:

Please Login or Register  to view this content.

[Bernie Deitrick]

Download the file - it's free, and it has all the zipcodes



https://public.opendatasoft.com/expl...gitude/export/

[Terrapin69]

I need something that will take my list of addresses without zip codes and automatically find them - will this do that?

[Terrapin69]

and by the way that link does not work

[Bernie Deitrick]

Here are the files - Zip, City state, Lat and Long. You can find the zips from the City State of your addresses and go from there.

[Terrapin69]

Thanks! But is this something I could actually do in excel? My current record, for example, looks like this:

4300 W Chicago Ave., Chicago, IL

I added a column for ZIP in Google sheets and I will need a query/script to look at all 3000 addresses and populate the zip codes.

[Bernie Deitrick]

Are you using Excel or Google Sheets? I can tell you how to do it in Excel....

[Terrapin69]

Excel preferably!!! and thanks

[Bernie Deitrick]

This example is based on the third file that I uploaded - choose your file based on where you are actually located, to get the most zip code matches.

[Terrapin69]

Thanks! Was wondering how to download all of the zip files (so I have everything) into one excel sheet and have a simple table that looks like the attached- I could simply clean up the master list, input the addresses, and the zips would populate - Can you help?

[Terrapin69]

It works perfectly in Google Sheets using my script:

function geo2zip(a) {
var response=Maps.newGeocoder()
.reverseGeocode(lat(a),long(a));
return response.results[0].formatted_address.split(',')[2].trim().split(' ')[1];
}
function lat(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lat
}
function long(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lng
}

However, and as expected, I get the ERROR: Service Invoked Too Many Times for One Day" after about 100 lines - I'd gladly upgrade my account to allow me to run this script for the whole sheet but can't find any info on how to do so.

[Terrapin69]

I downloaded all the files and put them into 1 excel column. I am using the following formula:

=MIN(IF($C$1:$C$531911=J7,IF($B$1:$B$531911=I7,VALUE($A$1:$A$531911))))

The ZIP column is showing 0

any ideas?

My example attached

[Terrapin69]

I downloaded all the files and put them into 1 excel column. I am using the following formula:

=MIN(IF($C$1:$C$531911=J7,IF($B$1:$B$531911=I7,VALUE($A$1:$A$531911))))

The ZIP column is showing 0

any ideas?

My example attached

apparently too big to attach but you get the idea I believe - Thanks

[Terrapin69]

This example is based on the third file that I uploaded - choose your file based on where you are actually located, to get the most zip code matches.

Thanks! On the right track. How would I add all of the other files in an earlier post to this sheet to get results like you did in your example?

[Bernie Deitrick]

Yes - too big, whick is why I made 5 files out of the data.

Note that you need to have entered that formula with Ctrl-Shift-Enter - it is an array formula. Note, too, that if nothing in C matches J7 and nothing in B matches I7, then the return will be 0. You can check the functionality by copying I7:J7 into B2:C2. Also, if you have text in A1 then the VALUE will return an error, so you may want to change all the $1 to $2.

Also, there were only 45000 zip codes in all the files - not sure why you have 531911 rows of data - that will really slow down your calculations.

[Terrapin69]

Gotcha and I think I have it working pretty well now. My next step, as I have briefly mentioned, is to take my existing list of 3500 or so addresses without zip codes and assign zip codes to them - No radius functions needed - Can this do the same thing?

[Bernie Deitrick]

The array-formula that we last discussed - with the MIN - uses the city and state to return the zip code.

[Terrapin69]

Can you give me a quick example based on the attached? I would only need to clean up the C column in my 3500 entries and have the D column, in green find the ZIP. I assume I would need to import all of the 5 excel files previously downloaded and out them into this sheet too...

[Bernie Deitrick]

Your data is not well structured.

To harvest some zips, use this in D2:

=IFERROR(TEXT(VALUE(RIGHT(B2,5)),"00000"),"")

and copy down. For those that return "" you will need to have a cell with the city and another with the state - which means you'll need to clean up your data set.

[Terrapin69]

Yeah - I know! The data was exported from smartsheet and it's the result of years of improper record keeping - I'll see what I can do and report back but many many thanks for so far

[Terrapin69]

That formula return a zip code if there is a zip in column B

what I need is a zip populated based on Column C where the zip is missing.

I assume I could have columns in the sheet which would house all the data we already downloaded (the 4500 or so rows) and the formula in D2 would compare against that?

[Terrapin69]

Like I said, this script works great in Google Sheets but times out after a few runs:

function geo2zip(a) {
var response=Maps.newGeocoder()
.reverseGeocode(lat(a),long(a));
return response.results[0].formatted_address.split(',')[2].trim().split(' ')[1];
}
function lat(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lat
}
function long(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lng
}

Is there any way to do something like that in Excel?

[Bernie Deitrick]

Create a column of formulas with this in Row 2:

=TRIM(RIGHT(SUBSTITUTE(TRIM(C2)," ",REPT(" ",LEN(C2))),2*LEN(C2)))

Then copy down to match your list in C, convert the formulas to Values and split the column using Text to columns delimited, with space and comma. That may find city and state for many of the rows where you don't have a zip code.

[Terrapin69]

Just not following sir, if possible can you take my attachment and do it on one row?

The end result needs to look like...

Subcontractor Mailing Address Full Address Zip Code

1 4300 W Chicago Ave,. Chicago, IL 4300 W Chicago Ave., Chicago, IL 60601

I need something in D2 that will pull zip codes from C2

[Bernie Deitrick]

Try this file, with some of the zip code list...

[Terrapin69]

Thanks much - I have burdened you enough and truly appreciate the help. I will continue to play around with this. The really odd thing is that I have the GSUITE paid version which allows my script to run 3200 times a day but I'm getting this error: Service invoked too many times in one day.geocode.Line 7 after it runs about 150 times - The scripts works great up until then. Maybe there is something in the script itself that is limiting?

=geozipmh(D2) - Cell E2

Script:

function geozipmh(a) {
var response=Maps.newGeocoder()
.reverseGeocode(lat(a),long(a));
return response.results[0].formatted_address.split(',')[2].trim().split(' ')[1];
}
function lat(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lat
}
function long(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lng
}

[Terrapin69]

Try this file, with some of the zip code list...

So...with this - can I just download the rest of the files in an earlier post, add them to column M-Q and have all of them?

[Bernie Deitrick]

Yes - just make sure that your formulas reference the new range of values or you may miss some.