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

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

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.

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:

=SQRT(((B3-VLOOKUP($E$2,$G$3:$J$36,2,FALSE))^2)+((C3-VLOOKUP($E$2,$G$3:$J$36,3,FALSE))^2)+((D3-VLOOKUP($E$2,$G$3:$J$36,4,FALSE))^2))

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:

Sub MinOfGates()
Dim LastGateRow As Double
Dim GateCtr As Double
Dim LastSpeedRow As Double

LastGateRow = Cells(Rows.Count, "G").End(xlUp).Row
LastSpeedRow = Cells(Rows.Count, "A").End(xlUp).Row

    For GateCtr = 3 To LastGateRow
        Range("E2") = Cells(GateCtr, "G")
        Application.Calculate
        Cells(GateCtr, "K") = WorksheetFunction.Min(Range(Cells(3, "E"), Cells(LastSpeedRow, "E")))
    Next GateCtr

End Sub

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

See attached. Hope it all helps.

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

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

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.