Find the nearest location to another location

[lucky13pierre]

In sheet 1 I've got a list of the locations I need to allocate people to. I've got a latitude and longitude location for each one. On sheet 2 I've got a list of the people with their locations in latitude and longitude. Example below

SHEET 1
Post Code Lat Lon Allocated to
SW1X 51.498 -0.153

SHEET 2
Name Post Code Lat Lon
Pierre RM20 51.478 0.289


There are about a couple of hundred locations and around a dozen people. I need to put a formula in the 'Allocated to' column to find out which of my colleagues are closest to each location easily. I've got a formula [=3960*ACOS(COS(RADIANS(90-C3))*COS(RADIANS(90-C4))+SIN(RADIANS(90-C3))*SIN(RADIANS(90-C4))*COS(RADIANS(D3-D4)))] to find out distance, and can use the =min() function if I was putting each post code in one at a time but that would be very time consumung.

 

[Anthony47]

I think you will find the task be more complex than determining which person is nearest the location.

Anyway, this would be my approach...
1) Create a table with the locations in vertical and the list of the persons, with their coordinates, in horizontal (as in the image)

2) Calculate the distance between the sites and each of the people:
In F5

=3960*ACOS((SIN(RADIANS($B5))*SIN(RADIANS(F$2))+(COS(RADIANS($B5))*COS(RADIANS(F$2))*COS(RADIANS(F$3-$C5)))))

(this is a simplified version of your formula)
Copy F5 to the right for as many persons are listed

Copy the first line of formulas down for all the locations

3) Use conditional format to highlight the best match for each of the lines:
Select the whole distance table, from F5 to Qxxx (if 12 persons are listed)
Set a conditional format, "Using a formula", using the following formula:

=F5=MIN($F5:$Q5)

Assign a background color Green and complete the formatting

Now you should see for each site who is the nearest person

4) Populate column D with the "Allocated to" name:
In D5 use the formula

=INDEX($F$1:$J$1,MATCH(MIN($F5:$Q5),$F5:$Q5,0))

Copy D5 down

That should give for each site who is the nearest "as the crow flies"

Now the simpe task is review the result to consider the street distance non the "crow fly distance" and to balance the workload...

MULTI_C30207.xlsm
	A	B	C	D	E	F	G	H	I	J
1						Louis	Phil	Michel	Marie	Denise
2					Lat	44,5678	44,6789	45,0123	44,9876	45,0023
3					Long	3,4567	2,3876	3,0012	3,6789	3,3322
4	Location	Lat	Long	Nearest
5	Site1	45,6666	2,1234	Michel		99,98	69,47	62,16	88,97	74,55
6	Site2	44,4321	2,2345	Phil		60,98	18,65	55,01	80,67	66,78
7	Site3	46,1111	3,3333	Denise		106,83	109,11	77,63	79,43	76,63
8
9
Foglio3

Cell Formulas
Range	Formula
F5:J7	F5	=3960*ACOS((SIN(RADIANS($B5))*SIN(RADIANS(F$2))+(COS(RADIANS($B5))*COS(RADIANS(F$2))*COS(RADIANS(F$3-$C5)))))
D5:D7	D5	=INDEX($F$1:$Q$1,MATCH(MIN($F5:$Q5),$F5:$Q5,0))