Create Territories

[markoakes]

I have a list of several hundred customers with Latitude and Longitude for each. I need to create sales Territories that have an equal number of customers and are grouped geographically. Any suggestions on the best way to accomplish this using just Excel?

 

[DRSteele]

To begin, you can determine the distances between each customer and the others. Put that in a matrix.

Please peruse this thread and its threads in the Forum for the formula to calculate Latitude and Longitude distances. http://www.mrexcel.com/forum/excel-questions/960529-help-countif-sumif-over-2-columns.html

After you create the matrix, we can try some optimisation techniques using Solver.

 

[DRSteele]

I took the liberty of creating a sample matrix for you.

Copy E7 and I7 down. Copy B15 across and down.

	A	B	C	D	E	F	G	H	I
h	m	s	Latitude	h	m	s	Longitude
Calgary
Edmonton
Montreal
New York
Seattle
Vancouver
Calgary	Edmonton	Montreal	New York	Seattle	Vancouver
Calgary
Edmonton
Montreal
New York
Seattle
Vancouver

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]51[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]46[/TD]
[TD="bgcolor: #E2EFDA, align: right"]51.04611[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]
[TD="bgcolor: #E2EFDA, align: right"]114.0567[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]53[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]43[/TD]
[TD="bgcolor: #E2EFDA, align: right"]53.54528[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]21[/TD]
[TD="bgcolor: #E2EFDA, align: right"]113.4892[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]45.50167[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #E2EFDA, align: right"]73.56722[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]46[/TD]
[TD="bgcolor: #E2EFDA, align: right"]40.71278[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[TD="bgcolor: #E2EFDA, align: right"]74.00583[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]22[/TD]
[TD="bgcolor: #E2EFDA, align: right"]47.60611[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]55[/TD]
[TD="bgcolor: #E2EFDA, align: right"]122.3319[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]19[/TD]
[TD="bgcolor: #E2EFDA, align: right"]45.63861[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="bgcolor: #E2EFDA, align: right"]122.6614[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]281[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3019[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3259[/TD]
[TD="bgcolor: #C6E0B4, align: right"]711[/TD]
[TD="bgcolor: #C6E0B4, align: right"]874[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: #C6E0B4, align: right"]281[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2973[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3256[/TD]
[TD="bgcolor: #C6E0B4, align: right"]908[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1098[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #C6E0B4, align: right"]3019[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2973[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]534[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3674[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3760[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: #C6E0B4, align: right"]3259[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3256[/TD]
[TD="bgcolor: #C6E0B4, align: right"]534[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3866[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3922[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="bgcolor: #C6E0B4, align: right"]711[/TD]
[TD="bgcolor: #C6E0B4, align: right"]908[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3674[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3866[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]220[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="bgcolor: #C6E0B4, align: right"]874[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1098[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3760[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3922[/TD]
[TD="bgcolor: #C6E0B4, align: right"]220[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=B7+C7/60+D7/3600[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I7[/TH]
[TD="align: left"]=F7+G7/60+H7/3600[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]=ACOS(COS(RADIANS(90-INDEX($E$7:$E$12,MATCH($A15,$A$7:$A$12)))) *COS(RADIANS(90-INDEX($E$7:$E$12,MATCH(B$14,$A$7:$A$12)))) +SIN(RADIANS(90-INDEX($E$7:$E$12,MATCH($A15,$A$7:$A$12)))) *SIN(RADIANS(90-INDEX($E$7:$E$12,MATCH(B$14,$A$7:$A$12)))) *COS(RADIANS(INDEX($I$7:$I$12,MATCH($A15,$A$7:$A$12))-INDEX($I$7:$I$12,MATCH(B$14,$A$7:$A$12)))))*6371[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[markoakes]

Thank you for your help so far. I understand how to create the matrix but I have not used the Solver add-in before. My matrix has about 10,000 accounts and I need to create 40 Territories with the tightest geography and about the same number of accounts. Any hints on how to do that with the Solver? Thanks again.

 

[DRSteele]

Stay tuned while I think it through. This kind of multi-modal optimisation is vexing.

 

[markoakes]

Thank you once again for your help. I will stay tuned.

 

[DRSteele]

I have made some headway. Forgive the massive amount of data here; I had to create enough data to properly conduct experiments. I made 12 locales and divided them into 3 regions. I see that you wish to divide your 10,000 locales into 40 regions. Notice that I put random numbers in the region field starting in B37.

Copy E7, I7, E41, F41 down. Copy B22 across and down. After that, we will invoke Solver.

	A	B	C	D	E	F	G	H	I	J	K	L	M
Calgary
Edmonton
Helena
Salt Lake
Montreal
Ottawa
New York
Washington
Seattle
Spokane
Vancouver
Victoria
Calgary	Edmonton	Helena	Salt Lake	Montreal	Ottawa	New York	Washington	Seattle	Spokane	Vancouver	Victoria
Calgary
Edmonton
Helena
Salt Lake
Montreal
Ottawa
New York
Washington
Seattle
Spokane
Vancouver
Victoria
Regions
Calgary	Locales
Edmonton	Locales per region
Helena
Salt Lake
Montreal
Ottawa
New York
Washington	total:
Seattle
Spokane
Vancouver
Victoria

<tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #FFF2CC"]locale[/TD]
[TD="bgcolor: #FFF2CC"]h[/TD]
[TD="bgcolor: #FFF2CC"]m[/TD]
[TD="bgcolor: #FFF2CC"]s[/TD]
[TD="bgcolor: #FFF2CC"]Latitude[/TD]
[TD="bgcolor: #FFF2CC"]h[/TD]
[TD="bgcolor: #FFF2CC"]m[/TD]
[TD="bgcolor: #FFF2CC"]s[/TD]
[TD="bgcolor: #FFF2CC"]Longitude[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]51[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]46[/TD]
[TD="bgcolor: #E2EFDA, align: right"]51.046[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]
[TD="bgcolor: #E2EFDA, align: right"]114.057[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]53[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]43[/TD]
[TD="bgcolor: #E2EFDA, align: right"]53.545[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]21[/TD]
[TD="bgcolor: #E2EFDA, align: right"]113.489[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]46[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]
[TD="bgcolor: #E2EFDA, align: right"]46.596[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]37[/TD]
[TD="bgcolor: #E2EFDA, align: right"]112.027[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]40.750[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]111.883[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]45.502[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #E2EFDA, align: right"]73.567[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]45.417[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]75.683[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]46[/TD]
[TD="bgcolor: #E2EFDA, align: right"]40.713[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[TD="bgcolor: #E2EFDA, align: right"]74.006[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]38[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]17[/TD]
[TD="bgcolor: #E2EFDA, align: right"]38.905[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]59[/TD]
[TD="bgcolor: #E2EFDA, align: right"]77.016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]22[/TD]
[TD="bgcolor: #E2EFDA, align: right"]47.606[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]55[/TD]
[TD="bgcolor: #E2EFDA, align: right"]122.332[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]32[/TD]
[TD="bgcolor: #E2EFDA, align: right"]47.659[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="bgcolor: #E2EFDA, align: right"]117.425[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]49[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]49.250[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]123.100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]43[/TD]
[TD="bgcolor: #E2EFDA, align: right"]48.429[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]56[/TD]
[TD="bgcolor: #E2EFDA, align: right"]123.366[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #A9D08E, align: right"]129668[/TD]

[TD="align: center"]22[/TD]

[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]281[/TD]
[TD="bgcolor: #C6E0B4, align: right"]517[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1157[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3019[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2876[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3259[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3168[/TD]
[TD="bgcolor: #C6E0B4, align: right"]711[/TD]
[TD="bgcolor: #C6E0B4, align: right"]449[/TD]
[TD="bgcolor: #C6E0B4, align: right"]674[/TD]
[TD="bgcolor: #C6E0B4, align: right"]729[/TD]

[TD="align: center"]23[/TD]

[TD="bgcolor: #C6E0B4, align: right"]281[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]780[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1428[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2973[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2838[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3256[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3194[/TD]
[TD="bgcolor: #C6E0B4, align: right"]908[/TD]
[TD="bgcolor: #C6E0B4, align: right"]711[/TD]
[TD="bgcolor: #C6E0B4, align: right"]819[/TD]
[TD="bgcolor: #C6E0B4, align: right"]894[/TD]

[TD="align: center"]24[/TD]

[TD="bgcolor: #C6E0B4, align: right"]517[/TD]
[TD="bgcolor: #C6E0B4, align: right"]780[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]650[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2941[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2785[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3096[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2957[/TD]
[TD="bgcolor: #C6E0B4, align: right"]787[/TD]
[TD="bgcolor: #C6E0B4, align: right"]425[/TD]
[TD="bgcolor: #C6E0B4, align: right"]875[/TD]
[TD="bgcolor: #C6E0B4, align: right"]875[/TD]

[TD="align: center"]25[/TD]

[TD="bgcolor: #C6E0B4, align: right"]1157[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1428[/TD]
[TD="bgcolor: #C6E0B4, align: right"]650[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3124[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2960[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3166[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2965[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1128[/TD]
[TD="bgcolor: #C6E0B4, align: right"]886[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1290[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1245[/TD]

[TD="align: center"]26[/TD]

[TD="bgcolor: #C6E0B4, align: right"]3019[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2973[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2941[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3124[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]165[/TD]
[TD="bgcolor: #C6E0B4, align: right"]534[/TD]
[TD="bgcolor: #C6E0B4, align: right"]786[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3674[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3316[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3686[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3726[/TD]

[TD="align: center"]27[/TD]

[TD="bgcolor: #C6E0B4, align: right"]2876[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2838[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2785[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2960[/TD]
[TD="bgcolor: #C6E0B4, align: right"]165[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]540[/TD]
[TD="bgcolor: #C6E0B4, align: right"]732[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3524[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3164[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3539[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3578[/TD]

[TD="align: center"]28[/TD]

[TD="bgcolor: #C6E0B4, align: right"]3259[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3256[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3096[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3166[/TD]
[TD="bgcolor: #C6E0B4, align: right"]534[/TD]
[TD="bgcolor: #C6E0B4, align: right"]540[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]326[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3866[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3499[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3903[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3931[/TD]

[TD="align: center"]29[/TD]

[TD="bgcolor: #C6E0B4, align: right"]3168[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3194[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2957[/TD]
[TD="bgcolor: #C6E0B4, align: right"]2965[/TD]
[TD="bgcolor: #C6E0B4, align: right"]786[/TD]
[TD="bgcolor: #C6E0B4, align: right"]732[/TD]
[TD="bgcolor: #C6E0B4, align: right"]326[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3738[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3370[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3791[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3812[/TD]

[TD="align: center"]30[/TD]

[TD="bgcolor: #C6E0B4, align: right"]711[/TD]
[TD="bgcolor: #C6E0B4, align: right"]908[/TD]
[TD="bgcolor: #C6E0B4, align: right"]787[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1128[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3674[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3524[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3866[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3738[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]368[/TD]
[TD="bgcolor: #C6E0B4, align: right"]191[/TD]
[TD="bgcolor: #C6E0B4, align: right"]119[/TD]

[TD="align: center"]31[/TD]

[TD="bgcolor: #C6E0B4, align: right"]449[/TD]
[TD="bgcolor: #C6E0B4, align: right"]711[/TD]
[TD="bgcolor: #C6E0B4, align: right"]425[/TD]
[TD="bgcolor: #C6E0B4, align: right"]886[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3316[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3164[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3499[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3370[/TD]
[TD="bgcolor: #C6E0B4, align: right"]368[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]454[/TD]
[TD="bgcolor: #C6E0B4, align: right"]450[/TD]

[TD="align: center"]32[/TD]

[TD="bgcolor: #C6E0B4, align: right"]674[/TD]
[TD="bgcolor: #C6E0B4, align: right"]819[/TD]
[TD="bgcolor: #C6E0B4, align: right"]875[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1290[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3686[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3539[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3903[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3791[/TD]
[TD="bgcolor: #C6E0B4, align: right"]191[/TD]
[TD="bgcolor: #C6E0B4, align: right"]454[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]93[/TD]

[TD="align: center"]33[/TD]

[TD="bgcolor: #C6E0B4, align: right"]729[/TD]
[TD="bgcolor: #C6E0B4, align: right"]894[/TD]
[TD="bgcolor: #C6E0B4, align: right"]875[/TD]
[TD="bgcolor: #C6E0B4, align: right"]1245[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3726[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3578[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3931[/TD]
[TD="bgcolor: #C6E0B4, align: right"]3812[/TD]
[TD="bgcolor: #C6E0B4, align: right"]119[/TD]
[TD="bgcolor: #C6E0B4, align: right"]450[/TD]
[TD="bgcolor: #C6E0B4, align: right"]93[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FFF2CC"]locale[/TD]
[TD="bgcolor: #FFF2CC"]Region[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="bgcolor: #FCE4D6, align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]region[/TD]
[TD="bgcolor: #FFF2CC"]count[/TD]
[TD="bgcolor: #FFF2CC"]distances[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]

[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]
[TD="bgcolor: #E2EFDA, align: right"]15225[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]

[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4243[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]

[TD="bgcolor: #FCE4D6, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]
[TD="bgcolor: #E2EFDA, align: right"]14020[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]

[TD="bgcolor: #FCE4D6, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #A9D08E, align: right"]33489[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]

[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]

[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]

[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]

[TD="bgcolor: #FCE4D6, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

latlong2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=B7+C7/60+D7/3600[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I7[/TH]
[TD="align: left"]=F7+G7/60+H7/3600[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A21[/TH]
[TD="align: left"]=SUM(B22:M33)/2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E37[/TH]
[TD="align: left"]=COUNTA(A37:A48)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E38[/TH]
[TD="align: left"]=E37/E36[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E41[/TH]
[TD="align: left"]=COUNTIFS($B$37:$B$48,D41)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F44[/TH]
[TD="align: left"]=SUM(F41:F43)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B22[/TH]
[TD="align: left"]{=ACOS(COS(RADIANS(90-INDEX($E$7:$E$18,MATCH($A22,$A$7:$A$18,0)))) *COS(RADIANS(90-INDEX($E$7:$E$18,MATCH(B$21,$A$7:$A$18,0)))) +SIN(RADIANS(90-INDEX($E$7:$E$18,MATCH($A22,$A$7:$A$18,0)))) *SIN(RADIANS(90-INDEX($E$7:$E$18,MATCH(B$21,$A$7:$A$18,0)))) *COS(RADIANS(INDEX($I$7:$I$18,MATCH($A22,$A$7:$A$18,0))-INDEX($I$7:$I$18,MATCH(B$21,$A$7:$A$18,0)))))*6371}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F41[/TH]
[TD="align: left"]{=SUM(($B$37:$B$48=D41)*TRANSPOSE($B$37:$B$48=D41)*$B$22:$M$33)/2}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Now invoke Solver using the following settings.
Set Objective click on $F$44
Min
By changing cells $B$37:$B$48
Subject to constraints: create four of them them by choosing the Add button
$B$37:$B$48 <= $E$36
$B$37:$B$48 = integer
$B$37:$B$48 >= 1
$E$41:$E$43 = $E$38
remove checkmark from 'Make unconstrained'
Select solving method as Evolutionary
ignore the other settings for now - the defaults should be adequate
click on Solve.

Solver should now vibrate around until it comes up with a solution. The outcome which should appear will be one where our first four locales are assigned to one region (one of 1, 2, 3) the second four to another, and the final four to a third. I happen to know that this will be the proper answer. This process should minimize the total distances to one another across the regions. Which, I believe, was our objective.

Give it a try and let us know how it goes. I anticipate that 10,000 locales and 40 regions is going to be markedly slower than our 12&3 experiment, if it even works at all.
<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]

 

[markoakes]

Wow, that looks like you spent a lot of time on this. Thank you . I am away from my computer for the weekend but will test this on Monday.