Finding closest location

[karattop]

I have a mileage matrix with all the locations across the top and down the side. On another sheet I have a stockout sheet, and I want to find the three nearest locations to the ones that are stocked out.

So, if I have a stockout (=IF(B3<0,.....) I want to go to the mileage matrix, find that location in the matrix (VLOOKUP or MATCH?), look down the row to find the lowest mileage (SMALL) and then at the top of that column is where my nearest location is (function??). In subsequent columns I want the second nearest location (SMALL(x,2)) and the third nearest location (SMALL(x,3)).

In parentheses I've indicated the formulas I've been playing with to try to afford this goal. I think this might be a little more complex than what I'm used to....

Maybe I am going about it in completely the wrong way. Any ideas?

 

[T. Valko]

I have a mileage matrix with all the locations across the top and down the side. On another sheet I have a stockout sheet, and I want to find the three nearest locations to the ones that are stocked out.

So, if I have a stockout (=IF(B3<0,.....) I want to go to the mileage matrix, find that location in the matrix (VLOOKUP or MATCH?), look down the row to find the lowest mileage (SMALL) and then at the top of that column is where my nearest location is (function??). In subsequent columns I want the second nearest location (SMALL(x,2)) and the third nearest location (SMALL(x,3)).

In parentheses I've indicated the formulas I've been playing with to try to afford this goal. I think this might be a little more complex than what I'm used to....

Maybe I am going about it in completely the wrong way. Any ideas?

Where is all the data we need to look for?

 

[karattop]

On the "Distance" sheet is the Mileage matrix. Row 1 has all the locations, and they are all repeated in column A. At the intersections of each row,col is the miles between the two locations, just like what you often see on maps.

On the stockout sheet, I have the location in column A and column B tells me if the location is stocked out by giving a negative number. So, if b3 is negative, in column C I want to see where the closest location is from where I can pull stock. In column D I want the second-closest location and in column E I want the third-closest location.

Eventually I will add what the stock is in those three locations and format according to which location has enough stock to ship to the OOS (out-of-stock) location.

 

[T. Valko]

On the "Distance" sheet is the Mileage matrix. Row 1 has all the locations, and they are all repeated in column A. At the intersections of each row,col is the miles between the two locations, just like what you often see on maps.

On the stockout sheet, I have the location in column A and column B tells me if the location is stocked out by giving a negative number. So, if b3 is negative, in column C I want to see where the closest location is from where I can pull stock. In column D I want the second-closest location and in column E I want the third-closest location.

Eventually I will add what the stock is in those three locations and format according to which location has enough stock to ship to the OOS (out-of-stock) location.

Try this...

Mileage matrix on the Distance sheet in the range A1:G10.

On your other sheet enter this formula in C3 and copy across to E3:

=IF($B3<0,INDEX(Distance!$B$1:$G$1,MATCH(SMALL(INDEX(Distance!$B$2:$G$10,MATCH($A3,Distance!$A$2:$A$10,0),0),COLUMNS($C3:C3)),INDEX(Distance!$B$2:$G$10,MATCH($A3,Distance!$A$2:$A$10,0),0),0)),"")

 

[number kruncher]

Is this something that suits your need?

At this stage I have not included any stock figures, just closest locations.

Excel Workbook
	A	B	C	D	E	F	G
1	3	1	2	3	4	5
2		Location A	Location B	Location C	Location D	Location E
3	Location A	0	15	17	20	14
4	Location B	15	0	12	21	25
5	Location C	17	12	0	23	15
6	Location D	20	21	23	0	11
7	Location E	14	25	15	11	0
8
9						1	Zero Count
10		Closest	Mileage	Nearest
11	Location C	1	12	Location B
12		2	15	Location E
13		3	17	Location A
Sheet

#VALUE!

 

[karattop]

Try this...

=IF($B3<0,INDEX(Distance!$B$1:$G$1,MATCH(SMALL(INDEX(Distance!$B$2:$G$10,MATCH($A3,Distance!$A$2:$A$10,0),0),COLUMNS($C3:C3)),INDEX(Distance!$B$2:$G$10,MATCH($A3,Distance!$A$2:$A$10,0),0),0)),"")

WOWZA! It works. It'll take me a little time to figure out how this works, but I'm gonna do it! I think I'll also try the other solution given by Number Kruncher just cuz.... I love to learn!

 

[T. Valko]

WOWZA! It works. It'll take me a little time to figure out how this works, but I'm gonna do it! I think I'll also try the other solution given by Number Kruncher just cuz.... I love to learn!

Don't let the length fool you!

If you get stuck just let me know and I'll walk you through it. Once you figure it out you'll then think: "heck, that's really quite simple and it makes perfect sense!".

 

[karattop]

Don't let the length fool you!

If you get stuck just let me know and I'll walk you through it. Once you figure it out you'll then think: "heck, that's really quite simple and it makes perfect sense!".

I get it! It did take me awhile to get used to thinking in relative positions. Ingenious how you don't literally find the column header, but you take advantage of the symmetry of the mileage matrix.

Thanks again!

 

[karattop]

Number Kruncher: Is 'Locations' a name? I've tried to imagine what it is, but have too many distractions today so I thought I'd just ask.

 

[T. Valko]

I get it! It did take me awhile to get used to thinking in relative positions. Ingenious how you don't literally find the column header, but you take advantage of the symmetry of the mileage matrix.

Thanks again!

You're welcome. Thanks for the feedback!