Spreadsheet: 186136d1349939338-delivery-scheduling-delivery-scheduling
Sheet: Formulae

Supplier
51 [NAME] A 768726
Starting Point | Starting Point | Starting Point | Starting Point | Starting Point
738 [NAME] | 738 [NAME] | 738 [NAME] | 738 [NAME] | 738 [NAME]
Monday | Tuesday | Wednesday | Thursday | Friday
15 Benoi Rd 629888 | 202 Ang Mo Kio Ave 3 560202 | Tuas South Ave 8 637558 | 15 Benoi Rd 629888 | 1001 Yishun Industrial Park A 768743 |  |  | Optimum timetable
50 Gul Rd 629351 | 347 Ang Mo Kio Ave 3 560347 | 50 Gul Rd 629351 | 50 Gul Rd 629351 | 51 [NAME] A 768726 |  |  | network model --> minimum spending tree
139 Woodlands Industrial Park E5 757506 | 127 Toa Payoh Lor 1 310127 | 139 Woodlands Industrial Park E5 757506 | 2 Benoi Place 629924 | 738 [NAME] |  |  | Travelling salesman problem
21 Woodlands Industrial Park E1 757720 | 745 Geylang Rd 389653 | 21 Woodlands Industrial Park E1 757720 | 11 Senoko Way 758032 |  |  |  | Vehicle routing problem
21 Marsiling Lane 730021 | 524 Hougang Ave 6 530524 | 10 Admiralty Street 757695 | 51 [NAME] A 768726 |  |  |  | Sweep + cumulative capacity load
113 Marsiling Rise 730133 | 322 Hougang Ave 5 530322 | 16 Tagore Lane 787476 | 347 Sembawang Rd 758355 |  |  |  | 90% + 10% reserve? Or just-in-time pattern
211 Marsiling Crescent 730211 | 51 [NAME] A 768726 | 22 Sin Ming Road 570022 | 33 Sembawang Rd 779084
51 [NAME] A 768726 | 807 [NAME] 760807 | 1 Clover Way 579077 | 202 Ang Mo Kio Ave 3 560202
11 Senoko Way 758032 | 51 [NAME] A 768726 | 51 [NAME] A 768726 | 347 Ang Mo Kio Ave 3 560347
20 Senoko Loop 758153 | 738 [NAME] | 738 [NAME] | 51 [NAME] A 768726
19 Woodlands Sector 1 738080 |  |  | 738 [NAME]
171 Yishun Ave 7 760171
51 [NAME] A 768726
738 [NAME]
Extraction Model
Nearest destination from [row address] |  | MIN(DistanceRoute!B5:AD5)
Column no. of nearest destination |  | MATCH(MIN(DistanceRoute!B5:AD5),DistanceRoute!B5:AD5,0)
Address of nearest destination from [row address] |  | INDEX(DistanceRoute!B3:AD3,MATCH(MIN(DistanceRoute!B5:AD5),DistanceRoute!B5:AD5,0))
Test Cases
 | Nearest destination from [row address] | Column no. of nearest destination | Address of nearest destination from [row address]
 |  |  |  |  |  | small (2) of D28
 |  |  |  |  |  | if = A28, small (2)+ if =D28, small (3)
 |  | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29
 |  | 738 [NAME] | 51 [NAME] A 768726 | 15 Benoi Rd 629888 | 50 Gul Rd 629351 | 139 Woodlands Industrial Park E5 757506 | 21 Woodlands Industrial Park E1 757720 | 127 Toa Payoh Lor 1 310127 | Tuas South Ave 8 637558 | 10 Admiralty Street 757695 | 2 Benoi Place 629924 | 11 Senoko Way 758032 | 20 Senoko Loop 758153 | 19 Woodlands Sector 1 738080 | 1001 Yishun Industrial Park A 768743 | 21 Marsiling Lane 730021 | 113 Marsiling Rise 730133 | 211 Marsiling Crescent 730211 | 171 Yishun Ave 7 760171 | 202 Ang Mo Kio Ave 3 560202 | 347 Ang Mo Kio Ave 3 560347 | 745 Geylang Rd 389653 | 524 Hougang Ave 6 530524 | 322 Hougang Ave 5 530322 | 807 [NAME] 760807 | 16 Tagore Lane 787476 | 22 Sin Ming Road 570022 | 1 Clover Way 579077 | 347 Sembawang Rd 758355 | 33 Sembawang Rd 779084
1 | 738 [NAME] |  | 2.4 | 32.8 | 32.3 | 6.2 | 6.3 | 17.1 | 38.1 | 5.6 | 30.9 | 6 | 6.6 | 5.6 | 2.6 | 8.7 | 8 | 9.3 | 1.6 | 8.7 | 9 | 20.4 | 15.9 | 15.6 | 1.7 | 3.3 | 11.2 | 11.8 | 1.4 | 4.1
2 | 51 [NAME] A 768726 | 3.1 |  | 34.5 | 34 | 6.7 | 6.8 | 18.5 | 39.8 | 5 | 32.6 | 6 | 7.1 | 5.6 | 1.1 | 9.3 | 8.6 | 9.9 | 2.3 | 10.1 | 10.4 | 21.8 | 17.3 | 17 | 3.8 | 9 | 13 | 13.6 | 3.1 | 5.8
3 | 15 Benoi Rd 629888 | 30.4 | 31.9 |  | 4 | 28.1 | 29.8 | 26.6 | 12.4 | 32.1 | 1.6 | 31.8 | 33.6 | 32 | 32.8 | 27.8 | 28.2 | 30 | 31.7 | 34.2 | 33.3 | 31.4 | 36.8 | 37.4 | 30.7 | 33.1 | 26.5 | 25.9 | 29.9 | 29.7
4 | 50 Gul Rd 629351 | 30.6 | 32 | 4 |  | 28.3 | 30 | 31 | 11.9 | 32.3 | 3.8 | 32 | 33.1 | 31.5 | 32.3 | 27.3 | 27.7 | 29.5 | 31.2 | 32.7 | 32.8 | 35.8 | 39 | 39.6 | 30.2 | 32.6 | 30.9 | 30.4 | 29.4 | 29.2
5 | 139 Woodlands Industrial Park E5 757506 | 6.7 | 7 | 29.6 | 29.1 |  | 0.95 | 24.3 | 34.9 | 4.1 | 27.7 | 3.3 | 2.8 | 2.4 | 6.7 | 2.5 | 3.2 | 3.1 | 6.2 | 16.3 | 16.3 | 27.6 | 23.1 | 22.8 | 7.9 | 12.9 | 16.9 | 17.5 | 6.5 | 9.2
6 | 21 Woodlands Industrial Park E1 757720 | 5.8 | 6.1 | 30.2 | 29.7 | 1.1 |  | 23.3 | 35.5 | 3.2 | 28.3 | 2.4 | 1.9 | 1.9 | 5.7 | 3.1 | 3.8 | 3.7 | 5.2 | 15.3 | 15.3 | 26.7 | 22.2 | 21.9 | 6.9 | 12 | 16 | 16.6 | 5.6 | 8.3
7 | 127 Toa Payoh Lor 1 310127 | 13.8 | 20.1 | 27.2 | 30.6 | 18.8 | 23.5 |  | 36.4 | 19.3 | 24.9 | 20.2 | 26.6 | 25.1 | 16.9 | 20.8 | 21.2 | 23 | 19.7 | 6.2 | 5.8 | 7.7 | 12 | 8.1 | 17.4 | 8.8 | 5.4 | 4.3 | 14 | 11.7
8 | Tuas South Ave 8 637558 | 34.4 | 35.9 | 10.3 | 9.8 | 32.1 | 33.8 | 34.8 |  | 36.1 | 10.1 | 35.8 | 36.9 | 35.3 | 36.1 | 31.1 | 31.5 | 33.3 | 35 | 36.5 | 36.6 | 39.6 | 46.6 | 47.2 | 34 | 36.4 | 34.7 | 34.2 | 33.2 | 33
9 | 10 Admiralty Street 757695 | 5.3 | 5 | 32.6 | 32.1 | 5.1 | 4 | 21.6 | 37.9 |  | 30.7 | 1.5 | 3.2 | 3.5 | 4.4 | 6.1 | 6.8 | 6.7 | 5 | 13.2 | 13.5 | 24.9 | 20.4 | 20.1 | 6.9 | 11.2 | 15.2 | 15.8 | 5.4 | 8.1
10 | 2 Benoi Place 629924 | 27.1 | 28.5 | 1.6 | 3.8 | 24.8 | 26.5 | 25.6 | 12.2 | 28.8 |  | 28.5 | 29.5 | 28 | 28.8 | 23.8 | 24.2 | 26 | 27.7 | 33.2 | 32.4 | 30.4 | 35.8 | 36.4 | 26.7 | 29.1 | 25.5 | 24.9 | 25.8 | 25.7
11 | 11 Senoko Way 758032 | 6.1 | 6.4 | 32.4 | 31.9 | 4.2 | 3.1 | 23.6 | 37.7 | 1.4 | 30.5 |  | 2 | 2.6 | 5.6 | 5.3 | 6 | 5.9 | 5.5 | 15.6 | 15.6 | 26.9 | 22.4 | 22.1 | 7.2 | 12.3 | 16.3 | 16.9 | 5.8 | 8.5
12 | 20 Senoko Loop 758153 | 7.1 | 7.4 | 31.1 | 30.6 | 2.1 | 3.1 | 24.6 | 36.4 | 3.2 | 29.2 | 2 |  | 3.2 | 6.6 | 4 | 4.7 | 4.6 | 6.5 | 16.6 | 16.6 | 28 | 23.5 | 23.2 | 8.2 | 13.3 | 17.3 | 17.9 | 6.9 | 9.6
13 | 19 Woodlands Sector 1 738080 | 5.2 | 5.5 | 30.1 | 29.6 | 2.3 | 2.4 | 22.7 | 35.4 | 3.5 | 28.2 | 2.7 | 2.7 |  | 5.1 | 4.8 | 4.1 | 5.4 | 4.6 | 14.7 | 16 | 26.1 | 21.5 | 21.3 | 6.3 | 11.4 | 15.4 | 16 | 5 | 7.7
14 | 1001 Yishun Industrial Park A 768743 | 2.9 | 0.65 | 34.4 | 33.9 | 6.6 | 6.7 | 18.3 | 39.7 | 4.8 | 32.5 | 5.8 | 7 | 5.5 |  | 9.1 | 8.4 | 9.7 | 2.2 | 10 | 10.2 | 21.7 | 17.2 | 16.9 | 3.7 | 8.8 | 12.8 | 13.4 | 3 | 5.7
15 | 21 Marsiling Lane 730021 | 8 | 8.3 | 27.3 | 26.8 | 2.7 | 3.6 | 21.2 | 32.6 | 6.8 | 25.4 | 6 | 5.5 | 4.2 | 7.9 |  | 1.3 | 3.3 | 7.4 | 14.2 | 14.1 | 25.5 | 21 | 20.7 | 9.1 | 10.8 | 14.8 | 15.4 | 7.8 | 9.5
16 | 113 Marsiling Rise 730133 | 7.8 | 8.1 | 27.5 | 27 | 2.6 | 3.6 | 21.8 | 32.8 | 6.8 | 25.5 | 6 | 5.4 | 4.1 | 7.9 | 2.1 |  | 3.7 | 7.2 | 13.9 | 13.8 | 25.2 | 20.7 | 20.4 | 8.9 | 10.5 | 14.5 | 15.1 | 7.6 | 9.2
17 | 211 Marsiling Crescent 730211 | 8.7 | 9 | 27.7 | 27.2 | 4.2 | 3.2 | 21.7 | 33 | 6.4 | 25.8 | 5.6 | 5.1 | 4.9 | 8.6 | 0.55 | 1.9 |  | 8.4 | 15 | 15 | 26.4 | 21.8 | 21.5 | 10.1 | 11.6 | 15.6 | 16.2 | 8.8 | 10.3
18 | 171 Yishun Ave 7 760171 | 1.7 | 1.4 | 33.5 | 33 | 6.2 | 6.3 | 17.9 | 38.8 | 4.8 | 31.6 | 5.8 | 6.9 | 5.4 | 1.6 | 8.8 | 8 | 9.4 |  | 9.6 | 9.8 | 21.2 | 16.7 | 16.4 | 3.2 | 8 | 12.9 | 12.2 | 2.1 | 4.8
19 | 202 Ang Mo Kio Ave 3 560202 | 8.2 | 9.9 | 28.3 | 31.9 | 14.1 | 15 | 4.9 | 37.7 | 13.3 | 27.3 | 13.7 | 14.8 | 13.3 | 10.1 | 14.2 | 13.4 | 15.8 | 9.4 |  | 0.85 | 11.3 | 6 | 6.6 | 7.2 | 4.1 | 2.1 | 3 | 8 | 7
20 | 347 Ang Mo Kio Ave 3 560347 | 8.7 | 10.4 | 33.4 | 35.9 | 14.4 | 15.4 | 6 | 41.7 | 13.8 | 27.7 | 14.3 | 15.3 | 13.8 | 10.6 | 14.5 | 13.7 | 15.9 | 10 | 1.2 |  | 9.7 | 5.6 | 6.2 | 7.7 | 5 | 2.5 | 3 | 8.5 | 7.9
21 | 745 Geylang Rd 389653 | 19.6 | 21.2 | 32.7 | 36 | 25.3 | 26.2 | 7.9 | 41.8 | 27 | 31.7 | 26.7 | 27.7 | 26.2 | 21.4 | 26.3 | 24.6 | 27 | 20.8 | 10.5 | 9.6 |  | 8.3 | 8 | 18.5 | 16.4 | 11 | 10.1 | 19.4 | 18.2
22 | 524 Hougang Ave 6 530524 | 13.1 | 15.8 | 36.9 | 38.3 | 19.8 | 20.8 | 10.1 | 44.1 | 21.6 | 32.5 | 21.3 | 22.3 | 20.8 | 16 | 19.9 | 19.2 | 21.6 | 15.4 | 6 | 6.1 | 9.7 |  | 1.5 | 13.1 | 10.2 | 7.8 | 8 | 13.9 | 12.7
23 | 322 Hougang Ave 5 530322 | 14.4 | 16.5 | 36 | 38.2 | 20.6 | 21.5 | 10 | 44 | 22.2 | 32.4 | 21.9 | 22.9 | 21.4 | 16.6 | 20.6 | 19.8 | 22.2 | 16 | 6.6 | 6.7 | 7.3 | 1.6 |  | 13.7 | 11.3 | 8.4 | 8.6 | 14.6 | 13.4
24 | 807 [NAME] 760807 | 1.7 | 3.5 | 32.3 | 31.8 | 7.4 | 7.4 | 15.5 | 37.7 | 6.8 | 30.4 | 7.2 | 8.3 | 6.7 | 3.8 | 9.9 | 9.4 | 10.5 | 2.9 | 7.1 | 7.4 | 18.8 | 14.3 | 14 |  | 6.7 | 10.4 | 9.8 | 1.7 | 3.6
25 | 16 Tagore Lane 787476 | 7.1 | 8.6 | 33.1 | 32.6 | 10.8 | 11.7 | 7.3 | 38.4 | 11.2 | 29.4 | 11.7 | 12.7 | 11.7 | 8.8 | 10.8 | 10.1 | 12.5 | 7.7 | 4.1 | 4.7 | 18.9 | 9.9 | 10.4 | 6.7 |  | 4.8 | 5.4 | 5.9 | 3.7
26 | 22 Sin Ming Road 570022 | 10 | 12.5 | 27 | 30.6 | 14.7 | 15.6 | 3.9 | 36.4 | 15.1 | 26 | 16.1 | 17.1 | 15.6 | 12.7 | 15.2 | 14 | 16.4 | 11.7 | 2.5 | 2.5 | 11.1 | 7.8 | 8.3 | 10.3 | 4.7 |  | 3 | 9.8 | 7.6
27 | 1 Clover Way 579077 | 10.4 | 12 | 26.4 | 30 | 15.3 | 16.2 | 3 | 35.8 | 15.8 | 25.4 | 16.7 | 17.8 | 16.2 | 12.3 | 15.4 | 14.6 | 17.1 | 12.3 | 2.9 | 2.9 | 9.6 | 8.2 | 8.7 | 9.4 | 5.3 | 1.9 |  | 10.5 | 8.2
28 | 347 Sembawang Rd 758355 | 1.4 | 2.9 | 31.6 | 31.1 | 6.1 | 6.2 | 12.5 | 36.9 | 5.5 | 29.7 | 5.9 | 7 | 5.5 | 3.1 | 8.7 | 8.6 | 9.3 | 2 | 9.4 | 8.3 | 19.7 | 15.2 | 14.9 | 1.7 | 6 | 10 | 10.6 |  | 2.9
29 | 33 Sembawang Rd 779084 | 6.1 | 7.6 | 31.9 | 31.4 | 9.6 | 10.5 | 9.8 | 37.2 | 10.2 | 30 | 10.6 | 11.7 | 10.2 | 7.8 | 9.7 | 8.9 | 11.3 | 6.7 | 6.7 | 7.2 | 18 | 13.5 | 13.2 | 5.7 | 3.3 | 7.3 | 7.9 | 4.9