| While I was at Note Expo last week I received a lot of positive feedback on my Excel videos. Its been awhile since I released one so I thought it was a good time to do a new video. When I receive a tape and am going through my initial filters, one of the things that I look at is the size of each city that the notes are in. I do this to filter out notes that are in small towns or rural areas. Other note investors don’t necessarily use this filter, but I like to only look at notes in towns that are over a certain size. My concern is that if the town is too small or rural then I could run into a number of problems:The home could be difficult to sell if I end up taking it backIt could be hard to find a good realtor in the areaGetting contractors or vendors out to the area could be expensive and challengingIts taken a lot of work, but I have created a large lookup table with city names and populations. When going through a tape, I copy this table into one of the tabs, and then I use the VLOOKUP function in Excel to add the population of the city each note is in to the tape. But there are many cities that have the same name in different states, so you can’t just do a lookup on city name. I add a new column and then combine the city and states to get a unique value. For example, in my lookup table Springfield Illinois is listed as SpringfieldIL. This differentiates it from the many Springfields in other states. This isn’t a foolproof method because there are many small population towns that are not rural and are part of large metro areas. I’m working to identify these towns so that I can add a go/no-go column to my lookup table, but I’m not there yet. If you have any ideas on how to do this I would love to talk about it. To get these videos on a regular basis, please subscribe to my YouTube channel by clicking the link below, or here: Fusion Notes YouTube Channel |
| Automating City Population Lookups in Excel |
| Dan Deppen |