Mapping US Cities

Another J-Walk YouTube playlist. These songs have US Cities in the title (except for two). I created a map of these cities using Excel and

First, I went to the playlist and copied all of the songs.

Then I pasted it into Excel

Each picture is a hyperlink, so I ran some code to extract it

It writes the hyperlink address one cell over then deletes the shape.

To fix the blanks, I selected all of the blank cells in column B, entered =B5, and committed the formula with Control+Enter. That copied all of the cells above the blanks.

Next I sorted on Column A and deleted any row that wasn’t a song.

I split all the song information on spaces so I could extract the city name.

That was a bust, so I just typed the city and state next to each song. I deleted duplicates and the two songs that didn’t really contain city names.

I downloaded the spreadsheet template from batchgeo and pasted my data into it

It processed all of the entries and I got this map

4 thoughts on “Mapping US Cities

  1. “I downloaded the spreadsheet template from batchgeo and pasted my data into it”

    Thanks for this. It solved a problem that’s been bugging me for 12 months, unrelated to Excel/VBA but I’m so glad to have it solved!

  2. There seems to be some differences in how browsers display the info in the pop-ups. In Chrome and IE, I get a clickable link, but it doesn’t work. In Firefox, I get no clickable link.

    All three browsers show the video URL in the pop-up, but it has to be copied and pasted to be of any use.

  3. For a moment I thought I’d finally found a way to create a map in Excel. I’ve been trying for several months to — forget geocoding — just map latitude and longitude into a Excel figure, preferably a chart but any kind of object would do to get started.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.