0

I have a spreadsheet full of physical US addresses that include zip codes.

I also a database of the estimated lat/long of all zipcodes in the US which can be found here: http://www.boutell.com/zipcodes/

I am looking for a VBA Solution to match the zip codes to the correct lat/long.

A statement that says "If zip codes match, enter the corresponding zipcode in new column".

simpresands
  • 3
  • 4
  • How come a VBA solution? If you have downloaded that .csv, you can do a formula (such as Index/Match) to return the GPS coordinates in your spreadsheet. – BruceWayne Oct 05 '15 at 16:49
  • Thanks for the quick response. I am open to any solution, just figured VBA was the easiest. Is there anyway you could walk me through the Index/Match process? – simpresands Oct 05 '15 at 16:52

1 Answers1

1

You can do this without VBA pretty simply. I assume you downloaded that .CSV with the zip codes/GPS coordinates.

In your existing spreadsheet with the Physical Addresses, copy/move the "zipcode" worksheet (open in Excel) to your existing sheet. Then, just use:

=IF($B2<>"",INDEX(zipcode!$D:$D,MATCH($B2,zipcode!$A:$A,0)),"") for Latitude, and =IF($B2<>"",INDEX(zipcode!$E:$E,MATCH($B2,zipcode!$A:$A,0)),"") for Longitude.

Note that I am assuming your Zip Code will be in column B...adjust the $B2 as needed.

Index/Match (which is similar to VLookup) works by returning some value from an Index. The zipcode!$D:$D is the list of Latitudes. The Match() part will tell the Index() part what Row to use. You're using the value in cell B2 and looking for it's match in the range zipcode!$A:$A, or the column of Zip Codes. When it finds a match, it returns the row number to the Index() formula...does that make some sense?

Edit: Here's a screenshot of how to use these. Since they're just formulas, you'll enter directly on your spreadsheet.

enter image description here

So, in your spreadsheet that you want to add the latitude and longitude to, add the formulas. Also make sure you've copied the zipcode.csv file into your workbook.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • I appreciate your help @BruceWayne. I am going to try this this afternoon. I will post the results – simpresands Oct 05 '15 at 16:56
  • Let me know how it works! Also, big thanks for your question because I can use that zip code database in something I've been working on! – BruceWayne Oct 05 '15 at 16:58
  • any chance you can rewrite the formula with the following info just so I can clarify I am doing correctly. - In my database, the zipcode is in column M. In the zipcode long/lat database, the zipcode is in column B and the Long/Lat are in columns J and K. – simpresands Oct 05 '15 at 17:03
  • Please disregard, I just read the bottom part in greater detail. That makes perfect sense. Thanks! – simpresands Oct 05 '15 at 17:09
  • 1
    @simpresands - Index/Match is a great formula to learn...for further clarification, check [this site](http://www.randomwok.com/excel/how-to-use-index-match/) and also [this site](https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/)...and of course there's always [Google](https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=index+match+excel). If this works, do you mind marking as the answer? – BruceWayne Oct 05 '15 at 17:24
  • Will do. I probably will not get a chance to work on it until this evening. Again, I appreciate the help and will make sure to mark answered once I get a chance to work on it. – simpresands Oct 05 '15 at 17:54
  • should I be writing this function in a module? A blank VBA code? Not sure where to enter in excel. – simpresands Oct 05 '15 at 18:06
  • This is a formula, so will go directly into your spreadsheet. If your sheet with Physical addresses (i.e. NOT the one downloaded from that site) has a column for latitudes, put the formula for latitudes in that column. I'll update my post with a picture, so you can see what I mean. – BruceWayne Oct 05 '15 at 18:09
  • This has been incredibly helpful. I am looking forward to solving the problem after work. Again, thanks! – simpresands Oct 05 '15 at 19:13
  • I would like to ask a separate question related to this project that I feel you could ask relatively quickly. Can I ask on this thread or should I start a new one? Please let me know if you are interested in helping. Thanks in advance – simpresands Oct 06 '15 at 18:18
  • For the zip code column, some of them have a more than 5 digit zip code so it does not register as a match. I would like to remove all numbers and (-) after the first five if they are present. – simpresands Oct 06 '15 at 18:19
  • If I understand, some of the zip codes include the extra four digits (i.e. `10012-4360`, and you just need `10012`? Just add this to the `Match()` part: `Index(zipcode!$D:$D,match(left($B2,5),zipcode!$A:$A,0))` ...you're just adding a `Left([zip code cell],5)` to the formula. Does that make sense? – BruceWayne Oct 06 '15 at 18:33