find the minimum distance between two points using two separate lists?

[drozle]

Is it possible to find the minimum distance between two points using two separate lists of different lengths without creating separate columns of equal row sizes

? I am trying to find the shortest distance form one point to another which is a fixed value. I am using the standard distance formula: =SQRT(((X2-X1)^2)+((Y2-Y1)^2)+((Z2-Z1)^2)).

My X1,Y1, Z1, has 2,000 rows in three columns that need to be compared to find the minimum distance from my one row/column of X2,Y2,Z2.

Is there a command that could go through and use each row of the X1, Y1, Z1, while looking for the MIN of the above formula without having to create an equal amount of rows for the X2, Y2, Z2?

I know I could do it by running this out the long way and then finding the min after I do all the math but I am wondering if there is an automated way for Excel to go through and do the forumla and then only return the MIN value?

Thank you in advance!

Dave

[MarvinP]

Hi Dave and welcome to the forum,

See if the attached isn't what you want. It calculates the distance to the other points, pulls out the min distance and tells what Other Point it is.

[drozle]

Actually, the issue is not the formula itself but the method in what I want to do and how to do it (thank you though Marv ).

Attached is the spread sheet in question . Columns B, C, and D is my aircraft trajectory while columns G, H, and I are the points of interest. I need to calculate the MIN value per each gate (Column F is the gate number). What I would like is to find a way for excel to run the distance formula (which I have as: =SQRT(((X2-X1)^2)+((Y2-Y1)^2)+((Z2-Z1)^2))). The issue I have is that I do want to have to make 1,936 rows of the same gate value just to then find the min value.

Is there a way to add a FUNCTION command that would have excel go through Columns B, C, and D, comparing them to each gate listed in G, H, and I and then only export the MIN value per each individual gate?

It would not be a big deal if I only had to do this once but I have to do this about 300 times for a test I am doing so I want to be able to only copy paste the flight trajectory data.

Thanks in advance

David

[MarvinP]

OK David - hold on to your hat - here is the answer(s). (I think )

Find the attached.
1. I made cell E2 the Gate number and the rest of column E is the distance between those coordinates.
2. Look at the formula in E3 and down as:

Please Login or Register  to view this content.

Which is long winded to go lookup the X,Y,Z gate coordinates based on their number in col G
3. You put in the Gate Number in E3 and all the distances are calculated for that gate.
4. A little VBA Loop supplies the rest. It goes down the gate numbers - putting them in cell E2 and then brings back the Minimum value of all those distances into col K across from the gate number.

Code looks like:

Please Login or Register  to view this content.

This is a GREAT example of letting Excel and VBA work together in an efficient way.

See attached. Hope it all helps.

[drozle]

Holly Moly Marv!

That is AMAZING!

So if I may ask, a few questions:

1. If I copy and paste new trajectories into the spreadsheet you provided, will it update accordingly?

2. Is there a way to do this without VBA? The reason I ask is I have 0 coding experience.

Many MANY thanks again Marv!

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[cas8100]

Marv,

I'm struggling with a similar problem and was hoping you'd be willing to lend your mind. I have a list of airports and their lat/long coordinates. For each airport, I am looking to calculate the closest airport from the remainder of the list and return both the identifier of that airport and the distance between the two. Cell D2 gives the formula for distance between locations. Right now, D2 is calculating the distance between A2 and A3. I thought about trying to do this as an array calculation and return the minimum value in the array, but it seems like the minimum value would always be 0 as the distance from itself is always 0.

Thanks for any help!

Carl

[arlu1201]

Cas8100,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[excelformula11]

working on opening it now!

Hi Dave and welcome to the forum,

See if the attached isn't what you want. It calculates the distance to the other points, pulls out the min distance and tells what Other Point it is.