• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel advice on difficult topic

I have the following list. For this list I need to put all distances between clubs. I can just fill it in one by one but then Ill do a lot of double work.

Cause when Ive looked up the distance of Aston Villa to Arsenal I do not have to search for the distance of Arsenal to Aston Villa. As it's the same distance.

Does anybody have advice for me about how I can make sure that when I filled in the distance Arsenal to Aston Villa I can automatically put that value in the row with Aston Villa to Arsenal?

Ideas are highly welcome!
 

Attachments

  • total_list_premier.xlsx
    16.2 KB · Views: 14
OK - so you are a football fan!

I've adapted your worksheet - I added my version of the calculation on the second sheet, entitled Fan Travel Distances. I've built a matrix of each team's name together with the Postcode of its ground. I then used a VBA Public function to get the distance from Google maps - the function includes a conversion as Google maps returns the distance between two points in meters, and I figure you'd want miles. BTW, I could also have given you travel time between each ground, but that gets a bit more involved .....

Now I'm not certain how you want to use the data, but if you give us a bit more insight, we can help you with some sort of "lookup" to get the distance from the Fan Travel Distances ...

By the way, thank you for posing this question - I've been wanting to do something similar and you prompted me to think about it ...
 

Attachments

  • total_list_premier - DME.xlsm
    37 KB · Views: 8
Been playing with this worksheet as I found the challenge interesting and the underlying material took me back to my days of watching football. Anyway, this is, as they used to say on WWTBAM, my final answer ... although I continue to believe it can be improved
 

Attachments

  • total_list_premier - DME.xlsm
    30.4 KB · Views: 4
Back
Top