Excel Zip Code Lookup

| 7 Comments | 0 TrackBacks

When creating applications for data entry, saving keystrokes for the user is
a worthy goal. One common task is entering addresses, which can be made easier and more reliable with zip code lookup. The idea is that the user only need enter the zip code and the application uses it to lookup the state and city information automatically.

excel_hero_zip_code_lookup.gif

 
The problem is that there are over 80,000 zip codes in the USA. This is too many to attack this problem with formulas. The performance would not be acceptable. This task is tailor made for VBA. In a similar technique as that used in the Partial Match Database Lookup, this project uses the AdvancedFilter method of the Range object. 

The technique is so fast that it barely blinks at this task, and happily creates dynamic lists of relevant city and towns, on the fly for each zip code entered - and these lists are used to feed validation drop lists.

The attached workbook contains virtually every zip code and community in the USA as a data source, which adds about 1.75 MB to each workbook that the data are used in - not very much in today's world.

Another benefit of this technique is that it requires very little coding. The workbook demonstrates how to use Structured Table References inside VBA Shortcut Range Referencing. I think the solution is elegant an highly useful.

Here's the file:





This incredible technique is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning. 

I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.

This Spring I will be offering the course again. There's already 150 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!


Note: if you cannot see the form, please click here.





If you liked this article, please share it!



Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/98

7 Comments

Very cool, and I like the tricky VBA techniques! While not a big user of tables in the past, I'll definitely be taking a look at their potential as you've demonstrated some cool functionality here. Great to see a new post despite you being so busy with other projects!

Why does the the Worksheet_SelectionChange event code begin with Exit Sub? Essentially, it is never running. So when a user selects another row, then their city drop-down is from the most recently changed zip code instead of containing the cities associated with the zip code on the selected row.

@Reuvain -

Nice catch! I had simply uploaded the wrong version of the file. That line was a remnant from my debugging session.

Thank you for noticing it!

The correct file is now uploaded, but you can simply delete the Exit Sub at the very top of yours.

Thanks, Tom!

Hui,

What application do you use to record the above example (record/play) tool.

The example was very useful , I am also interested in knowing the application you used to record/play.

regards

@Dhanush
I use Camtasia Studio 7
Which I know Daniel uses as well

I picked it up easily, it is very intuitive.

Thanks.

Apologies for mixing up the Excel gods.

regards

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on February 17, 2011 9:48 PM.

Chandoo's Awesome Excel School! was the previous entry in this blog.

Animated Business Chart #4 - Body Mass Index is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.