Excel advice on difficult topic

[marcvanderpeet12]

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!

 

[David Evans]

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

 

[David Evans]

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