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 BatchGeo.com.

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

Sub Gethlinks()
   
    Dim shp As Shape
   
    For Each shp In Sheet1.Shapes
        shp.TopLeftCell.Offset(0, 1).Value = shp.Hyperlink.Address
        shp.Delete
    Next shp
   
End Sub

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

Posted in Uncategorized

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.

Leave a Reply

Your email address will not be published. Required fields are marked *