You have 832 unique shops in your table. Therefore, there are total 832*832 -832 = 691392 combinations available. First you need to find out the distance between each shop to all other shops. Then you can filter the shop within close proximity of 3km.

Google api distance matrix can do the work for you but its paid like 4USD per 1000 hits. As you have around 691392 unique combinations then it will cost you around 2800 USD. Thats a huge amount. Earlier it was cheap and I created database of around 400000 combinations with just trial version of two different 300 usd accounts. I don't know if they still offer trial version.

You need to narrow down you shop combinations like there is no need to find the distance between shops that are in different city because that will be more than your limit of 3km. Once you've the distance database in place then further analysis can be done.

Hope this helps.

Wait, Just remember I used the api matrix because I had the city names instead of latitude and longitude. It can be done with excel without using google api distance matrix. Will get back to you with solution.

I used the vincenty formula to find the distance between two points using Latitude and Longitude. This is not the road distance between two points. You need to check if this meet your requirements. If not then you've to take help of google api distance matrix to perform the analysis.

Yeah its a very complex code and it requires Master level knowledge of Mathematics. I am also not able to fully comprehend it. You can visit this link to know more about the logic behind this code http://en.wikipedia.org/wiki/Vincenty%27s_formulae

Basically its a function and you can use it like any other formulae in excel. Just type =distVincenty() in any cell and pass the four parameters i.e lat1,lon1,lat2,lon2.

Once the database is ready then its fairly easy to find the shops within specified distance in Tableau.

Can this be displayed on a map too?

I made the Network Map for Source and Destination. Select any shop and all other shops within vicinity of selected distance will be displayed on Map. I filtered the database to shops that are within maximum distance of 20km. If you want to include greater distance then you've to prepare the data again in format used by tableau for Network Map.

Also, if the business requires a list of each shop in the format in my example table above is that also possible?

Prepared another sheet in Tabular format.

One more thing, if the shop locations change do I simply re-run the macro to update all the distances?

You have enter the =distVincenty() formula in Column G of master distance table if new shops are added to your dataset. There is no VBA code to do this. First prepare the format as shown in Column A-F of master sheet and then use the formula in Col G and drag it down to last row to find the distance between source and destination.

If this helps then please mark the answer correct or helpful.

Please start another thread for a new question. I will see what can be done.