You have help me a lot ! I’m making a cost sheet , using Kilometers from every city from state of São Paulo
@YogeshSureka4 жыл бұрын
Very structured & elaborate explanation. Must watch for all aspiring learners.
@srinivasannarayanaswami53783 жыл бұрын
Thank you Hemanth. Lucid explanation and a very useful tool. Much appreciated
@HEMANTHHEMU863 жыл бұрын
Thankyou sir
@leroyjenkins56792 жыл бұрын
Excellent hemant, just one observation though - Since api keys have limits, every call is precious. Hence filling only the upper half of the diagonal would have been wise. Also from efficient programming perspective it is important
@fulyaulutas45992 жыл бұрын
This is exactly what i needed. Thank you for explaining very clearly
@sudheerkumar64273 жыл бұрын
Good work Hemanth Boddapu... expecting more videos from you..
@himanshushukla34544 жыл бұрын
Thank you Hemanth for such insightful videos
@ssaravananp13 жыл бұрын
Dear Mr.Hemanth, It was a useful App. We have around 25 rows and columns in our Application. The macro works correctly upto 12 rows and columns. From 13th row and column, there is a difference of at the least 2 Kms. Please guide us. Regards, S.Saravanan
@blue01523 жыл бұрын
THANK YOU! You did miss that input for Source and Destination coordinates. Also you could have clarified the best way to get the coordinates in the database tab. OTHERWISE, this is an excellent video and I really appreciate it!
@vidaan95 Жыл бұрын
Hi Hemanth, this is a wonderful video. It really saved a huge amount of time for me.
@spvgggwdeg Жыл бұрын
This is amazing and very, very helpful for me. Thanks a lot for the well elaborated explanation.
@widhyawanjuliaris39392 ай бұрын
thank you very much for the knowledge. you've helped me in many ways.
@philcoul69053 жыл бұрын
Good work. This exactly what was looking for. Keep up.
@prafullgoyal243 жыл бұрын
Awesome...I need to hit like button for tonnes but have to satisfy with 1 only..✌
@HEMANTHHEMU863 жыл бұрын
Thankyou buddy 🤟🏻
@rajaaditya00212 жыл бұрын
Awesome sir! Helped a lot during SIP... Thank you so much!
@lucastavares34703 жыл бұрын
Greetings from Brazil 🇧🇷
@gouthamsolleti43163 жыл бұрын
Good work bro....working perfectly....
@siddharthamodukuri7112 Жыл бұрын
Thanks Hemanth, Very helpful
@sohamdsouza4 жыл бұрын
Great work Hemanth !
@aphastus2 жыл бұрын
Wow I tried not knowing if it’d work because I’ve never coded in VBA and I did a couple mistakes that I was able to detect and understand watching my code and yours (mostly cells placements). I used the time instead of the distance. It is incredibly satisfying watching the code fill all the cells in the matrix. I started in Mac but the getdistance formula didn’t work. I’ll try it later to see if something wasn’t correct. What bugged me is that every version of office uses different separators, you used a , and I had to change it to a ; so probably it’s the same problem with Mac. Anyway, thanks so much for the video, it was so nice!
@HEMANTHHEMU862 жыл бұрын
Hey.. the issue with mac is that excel in mac cannot talk to internet. Pls try with windows. I've mentioned the same in the description box
@TheArtlessArtist4 жыл бұрын
Lovely work Hemanth Keep up the good work ❣️
@Bhoomisolanki0210 Жыл бұрын
Very Helpful video, Thank you !
@karstenhaberkorn61823 жыл бұрын
Hello Hemanth, this is really very helpful and a great tool - thank you for that! Can you also advise, on how to get coordinates (latitude and longitude) back from bing maps for given locations? This would be very helpful, in case you need coordinates for many addresses (comparable to the list of locations you put into the "database sheet"). Many thanks for any advice! Karsten
@jacky_233 жыл бұрын
I had this problem and used power query web connector
@catquynhnguyenle993 жыл бұрын
Thank you so much for providing this video
@alexanderbarlse28752 жыл бұрын
Hi there! Thank you for this great piece of code, it helped me a lot. However, i'm having a small issue. The code was running perfectly well yesterday. However, men reopening the xlsm file today, and trying to use the getdistance function, I get the error: #NAME?. Do you have any suggestions? Thank you.
@jamesa75763 жыл бұрын
This is awesome! Thank you
@RodrigoSebastianBuchiniz2 жыл бұрын
Genial! Gracias muy útil y muy bien explicado!
@MrJaawt Жыл бұрын
right now the calculation automatically grabs the shortest distance and not the interstate distance. how could I change the VBS Module to show the shortest interstate distance? But thank you very much for this so far
@user-epslesvou19 күн бұрын
Hi ... i cant create a bing key. It s not working anymore...any help?
@adrianesc7706 Жыл бұрын
What if i don't have the coordinates?
@Michael-zg5cxАй бұрын
Hey Great work! I used your code for two years now. But with the change to Azure Maps I don't get this think working anymore... can you make a new video where you explain how to use Azure Maps API instead of Bing Maps?
@jenildesai55655 ай бұрын
This code is working for India's coordinates. But it is not working for USA coordinates. Please help.
@Gabriel-Oliveira3 жыл бұрын
Hi Mr. Boddapu. Firstly, I really appreciate the time you spent recording this video. It was really important for my understanding when it comes to create a distance matrix. I have a question, if I have 12 locations, I think the total number os distances would be 144 (12*12) instead of 66, am I right? Cheers from Brazil!
@HEMANTHHEMU863 жыл бұрын
yes 144, that's correct
@TheNitoperez Жыл бұрын
Desde Mexico.... funciona al 100%
@vigneshkr94973 ай бұрын
how to do this between pincodes within the city?
@bhargavagatla35836 ай бұрын
Hi.. Can we get the distances by pincode to pincode
@TrudiDay Жыл бұрын
Awesome - thank you!!
@younessfrichi21563 жыл бұрын
Thank you for the video. Please how can we get the travel time instead of the distance?
@HEMANTHHEMU863 жыл бұрын
Very simple, in the code i have attached, towards the end of the code, there is a phrase "//TravelDistance". You need to replace it with "//TravelDuration" and remove 1.609 multiplier
@younessfrichi21563 жыл бұрын
@@HEMANTHHEMU86 Thank you. It works :)
@nanakipshidze38983 жыл бұрын
@@HEMANTHHEMU86 thank you for great video. is it possible to calculate walking distance instead of driving distance?
@khinthiri10612 жыл бұрын
@@younessfrichi2156 Hello!! I also want to calculate the travel time. Could you please give me some reference coding for calculating the travel time instead of travel distance?
@user-ns4tw5lw1rАй бұрын
Why we used this all it's all available in map when we enter two cities distance in map it's showing
@lucastavares34703 жыл бұрын
Omg man , awesome work
@13_pritamsanyal415 ай бұрын
Hey, why the marix is not symmetric ?
@nicolesamson3802 жыл бұрын
This works perfectly, Thank you so much Hemanth. When im working on a larger data set it takes quite a lot of time for it to fetch the distance, is there any work around for that? TIA.
@ronnieencarnacion57293 жыл бұрын
Excel returned #value! when computing for the Distance. Followed the procedure. Concactenate and keys are correct. May i know if this is common issue and if it can be resolved. Thanks.
@HEMANTHHEMU863 жыл бұрын
If you are using excel on windows, this should not occur. once revisit the steps i have mentioned and make sure internet is active. Let me know
@reaper_gaming_live3 жыл бұрын
Same error with me how can we know if this not getting blocked due to some firewall issue etc
@HEMANTHHEMU863 жыл бұрын
@@reaper_gaming_live pls follow the steps. It works
@farooqa1143 жыл бұрын
I think error is due to the concatenate function. I used the cancat function but Hemanth used the & sign while getting the X Y coordinates....Kindly explain the destination and source coordinates please.
@jairopontes813 жыл бұрын
I was having the same issue. It happens that I use metric system in my pc, in which the decimals are separeted with a coma, not a point. I changed these parameters in the options tab and it worked!
@rohitdadi78988 ай бұрын
Can you share the link....of the code pls......hyperlink is missing
@Suman-vv5gv Жыл бұрын
Thanks for this Video ...
@ankurdikshit46693 жыл бұрын
followed all the steps, still after using formula excel is returning #value! error, may i know how can it be resolved
@puzzleswithnik83483 жыл бұрын
same
@shehanwijeyawansa13724 ай бұрын
Thank You
@2909remco3 жыл бұрын
Thank you for this great video. Would it be possible to also provide the associated GetTravelTime code? Much appreciated
@unnikrishnansanthosh2 жыл бұрын
hi Hemanth, thank you very much for sharing such an useful tutorial, great work. I would like to know whether you have a tutorial for calculating air distance
@unnikrishnansanthosh2 жыл бұрын
Also, one more query, what is the base for calculating travel time - like is it based on speed limits of the roads or peak time / off peak time traffic on the roads
@HEMANTHHEMU862 жыл бұрын
time is given by Bing, based on the traffic data. just like Google provides on estimated time.
@katieallinson57813 жыл бұрын
Hemanth - please please can you tell me how to make bing maps return the longest distance between two points.
@Alphabah952 жыл бұрын
Hello, I found your video very helpful for my project, I am a master's degree student. I had some issues linking the geographical coordinates to the distance Matrix like you did, can you explain to me or send me the excel file you did the demonstration on KZbin? so I can refer to that to build my own matrix? Thank you your help will be really appreciated.
@michalzila7084 Жыл бұрын
I tried it and i doesn't work. Variable not defined.
@guonorman51 Жыл бұрын
Thanks for your video clip
@ohoudsz3259 Жыл бұрын
Much appreciated it was very helpful, I was looking for this formula too many weeks and my manager he was very impressed about it 🤍
@grizzoff3 жыл бұрын
Amazing! Thank you!!
@eetigupta78402 жыл бұрын
Hi, your video helped me a lot in my client deliverable. Thanks a ton. Is there a possibility to get the travel time for a particular date and time? I am looking to calculate travel time for historical date and time
@Antizokker Жыл бұрын
Thanks for the Video! But how do i get the field like B11 and which are necessary for the automasation to fill out the Matrix??
@TomCat-hp6mi Жыл бұрын
I want to do something similar. I want to know the distance travelled by the train from station A to station B. Please tell me how to do it.
@meetyadav416 Жыл бұрын
Awsome dude 😮
@yigityldz1089 Жыл бұрын
im getting a theres a problem with this formula error. Can you help me please
@yigityldz1089 Жыл бұрын
I solved, put ; instead of , on your getdistance formula.
@ambarukanasubrahmanyam51 Жыл бұрын
excellent video
@faisalalkaabneh40203 жыл бұрын
this is amazing, thanks a lot :)
@irenesolmone7667 Жыл бұрын
Hi! I used the formula and everything worked perfectly well for a while. Now, excel isn't recognizing the formula anymore. Do you have any advice on how to solve this?
@sidneyro5554 Жыл бұрын
Hi, excellent tutorial. Would you help me .the negative coordinates of the error how do I?? positive coordinates works. thanks🙂
@josegutierrez49692 жыл бұрын
I LOVE YOU
@saikiran-zn4cq4 жыл бұрын
Thanks dude.
@DAANoLOOF2 жыл бұрын
Hi Hemmath, is the distance calculated over the road or in a straight line? Either way, thank you for the tutorial!
@NathanaelHarwood Жыл бұрын
Just for others' sake - the description states 'It provides the road distance along the best routes considering traffic'
@TAMALCHOUDHURYROY3 жыл бұрын
Hello Hemanth, In my pc excel 2013 / office 365, formula "=getdistance" is not showing Can you please help me how can I resolve this issue?
@HEMANTHHEMU863 жыл бұрын
I have provided a peice of VBA code in the description. Please do paste it in your vba window.
@ranjitsakhare Жыл бұрын
Hemant thanks a lot. Its helping a lot. Like function GetDistance can you help with GetTime as well?
@narejo2232 жыл бұрын
lastVal = "&travelMode=driving&o=xml&key=" & key & "&distanceUnit=mi" if for meters than what will be correct code instead of miles
@bubblesarita1428 Жыл бұрын
Excellent👍
@WorkEasyGo Жыл бұрын
Hi How did you connect database to sheet one. Can someone please help me out with the formula.
@infinitedata70883 жыл бұрын
It is very useful, but Im facing a error actually my all study locations are within of 4 km range. I need accurate ans in upto 2 decimal.
@HEMANTHHEMU863 жыл бұрын
In the code i have given, there is an equation similar to the one i have pasted below. Just replace this line with the existing line to get a more accurate reading. GetDistance = Round(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.609, 2)
@infinitedata70883 жыл бұрын
@@HEMANTHHEMU86 Thank you very
@tommyexceltricks76652 жыл бұрын
Hello , Thanks for this tutorial can i ask about something Q1:why ther's diffrence between bing maps and Google maps Ex: two cordination ( lat,long) in bing maps : 16 Km -- Google maps : 13 Q2:some times result returned from bing maps doesn't exact the result already in bing maps if you switched to bing maps and tried to get direction to comapre result in excel file with data in bing maps thanks in advance
@hasinrizwan57242 жыл бұрын
Great video brother, Very helpful for me... Can you tell how to convert this Excel data into numpy array?
@RafiqueNoorie3 жыл бұрын
It is not mapping the distance which you was taken an another excel sheet2. How we can merge into excel sheet 1?
@matinamoozadeh2353 жыл бұрын
my excel shows the error Sub distMatrix does not define, what is the problem?
@superintendentofpolicememb35603 жыл бұрын
Thanks but I follow your instruction but GetDistance formula not shown in Excel-2019
@arvindshaker19842 жыл бұрын
hi it was used full to me but it is not working on my excell pls give excel which is red to used
@gokaykkaya8 ай бұрын
can this method be used for 80 locations?
@hakanyldz99773 жыл бұрын
Can we draw route line inside the excel bing map for to make travelling salesman problem?
@sergio_molinari2 жыл бұрын
Hello great tutorial!! I noticed that it considers the Highways, how can I not consider them in the search? Thank you
@osamarizwan16352 жыл бұрын
how can we get the distance in meters?
@rasraster Жыл бұрын
Very nice, thank you. Can this be done with HTTPS instead of HTTP?
@SiBull Жыл бұрын
How to get coordinate automatically trough excel?
@ahmadutsmansiddiq434410 ай бұрын
Hi, I really love your tutorials, but why do I get the output "-1" instead of the real travel distance? I already did exactly the same as shown in your video. Thank you
@HEMANTHHEMU869 ай бұрын
i am guessing, in the input data, some formatting might be needed
@jenildesai55655 ай бұрын
@@HEMANTHHEMU86hi i am also getting the same error. I checked the formatting it's same as yours. Still getting -1 as the output. Please help
@jacky_233 жыл бұрын
Good video !!
@Topcommi3 жыл бұрын
Hi, thanks. In small distance it works not good, because when I have 4,5km it shows me 5km.. Can u make a video how to get the minutes from the distance.
@HEMANTHHEMU863 жыл бұрын
In the code i have given, there is an equation similar to the one i have pasted below. Just replace this line with the existing line to get a more accurate reading. GetDistance = Round(Round(WorksheetFunction.FilterXML(objHTTP.ResponseText, "//TravelDistance"), 3) * 1.609, 2)
@Topcommi3 жыл бұрын
@@HEMANTHHEMU86 Thanks great work. It is possible to calculate the travel time with car in excel ?
@HEMANTHHEMU863 жыл бұрын
@@Topcommi yes travel time is possible
@Michael_Hamburg2 жыл бұрын
@@HEMANTHHEMU86 is it possible to do this for public transport too?
@thatgoanguy44276 ай бұрын
Im getting #value error, idk why
@ghanshyamsinhdvadher31192 жыл бұрын
Thanks great help, can u check the same can be converted into AddIn, i was unable to do it
@ghanshyamsinhdvadher31192 жыл бұрын
kzbin.info/www/bejne/jp-9Yomwaaypp7s, this would be helpful
@almemo92 жыл бұрын
Juest amazing, you save me two days of work, just an small question, for big volume of data, the formula gives me #value message, any ideas? thanks in advance
@gowthamseshadri51182 жыл бұрын
How to get the coordinates for any locations using API. It is really difficult to get lat & long for every locations manually. Please guide me.
@HEMANTHHEMU862 жыл бұрын
It's not difficult. There are Google and Bing APIs for that
@anitasunildesai2 жыл бұрын
hello Hemanth, can we do the same giving lat and long instead of name of cities? how?
@dindazalfa2 жыл бұрын
Hello Hemanth, can you help me to make vba code for calculate distance and travel time without toll usage in BingMaps?
@alejandrapinoncely70402 жыл бұрын
Hi! great job! The code is working but not for every city. I am having trouble with cities in Asia, more specifically within China, Japan and Korea Could you help me with that?? Regards, Alejandra
@subhendupradhan64753 жыл бұрын
it is throwing an error "invalid outside procedure". Please Help
@renatobeltrame54032 жыл бұрын
Hi Hemanth! Thank you for sharing!! I have a few questions I think you could help. For the application in excel that I'm developping I need the distances to be showed in meters and with the most accuracy possible. Reading your code I understod that the distances are being calculated in miles and rounded to no decimals. Am I right? If your answer is yes, could you help me to do these adjustments? Thank you very much!
@renatobeltrame54032 жыл бұрын
Hi, it's me again! Reading the code with more attention I realized that you already converted the dimensions to meters and I changed de round rule. Thanks
@uyenphamthithuuyen19032 жыл бұрын
Hi, your video is really helpful. However, I don't know how to obtain the longitude and latitude from Bing Maps. Can you create a video how to do that, please?
@filippomauro48462 жыл бұрын
in excel you can write the location in a cell (es. Rome), than select the cell that contain the location, go to data-->data types-->geography and extrapolate latitude and longitude clicking on small icon that appears next the location cell. In a third column you can use the function =concat(cell latitude, "," ,cell longitude) and you have the coordinate of location
@hakanyldz99773 жыл бұрын
After you copy and paste key password on the cell B9 then automatically appeared word "KEY" on cell A9. How do it?
@HEMANTHHEMU863 жыл бұрын
It was not automatic.. i have written it in that cell.. i have fast forwarded the video at bit over thete
@sherifhassan15563 жыл бұрын
Hemanth I hope you're well. This is an excellent tutorial, thank you. Unfortunately, I keep getting #NAME? error when running the GetDistance macro. Any suggestions? Thanks again. Syh
@swaaditya3 жыл бұрын
The tool is great however, can we get the longitude and latitude automatically from bing maps or google maps? As, for all 1000 plus service locations we cannot manually input them right?
@HEMANTHHEMU863 жыл бұрын
If you mean getting coordinates from addresses, there are similar ways to do it. But it's accuracy is low.. i have observed it gets offset by 10-12 kms sometimes
@swaaditya3 жыл бұрын
@@HEMANTHHEMU86 If thats the case then for each and every address there needs to be an input provided manually. Please share further insight by mailing me on swaaditya@gmail.com. I would be of great help as am trying to work on route optimization in my project as well