Solutions
Virus Scan
Fixes old bugs, adds new features:
- Create radius circles from your warehouses
- Variable inclusion-exclusion radius
- Separate reports for inside/outside defined radius
- ETA and progress indications*
- More stable
- Faster
- and probably more!
MapPoint Distribution Optimizer Excel add-in to plot the closest warehouse location for a customer.
Input: A Microsoft MapPoint 2002 map with at least two layers
(a multiple pushpin type layer only counts as one layer, not however many different markers MapPoint may use. sorry.)
Output: An Excel spreadsheet with all facility information (any fields you imported) with the closest warehouse location and distance.
I use this at my job to optimize our assignment of customers across approx. 100 distribution centers.
- Create yourself a map with your distribution centers as pushpins.
- Save that, you can use it many times.
- Whenever you need to assign a bunch of customers to their closest warehouse (for bids, weekly leads, yearly review, etc.), plot those customers on the same map as a new layer.
- Save that map, and close MapPoint.
- Open Excel, and click on the MapPoint button.
- A form will come up where you can enter the location of your map. If you don't remember or don't want to type, click on the button with 3 dots on the right-hand side of the Map Name box.
- You can browse until you find your map, then either double-click, or select and click Open
- The map filename will be displayed in the Map Name box, make sure its right and click Open
- You will have to wait a few seconds for the map to open, and it may take a couple of minutes if you have a linked datasource. (Note: this has not been tested on dynamic data. It has been used on a linked datasource, but only with a static database. It may not work and/or may cause problems or appear to lock up if it is used on a dynamic database, especially if there are import problems)
- Once the map is loaded, the Layer boxes will have the names of each data set that is on the map. Pick the Distribution layer (the layer with your warehouses), and select the field in your import data that will identify the location. (in my use, I have a unique warehouse name: "Boston", "Detroit", etc.)
- Once both parts of the distribution are selected, pick which layer contains the facilities that you are trying to optimize.
- The two layers cannot be the same layer, location A will always be the closest to location A within the same layer.
- Finally, you can select which worksheet you want the data exported to. This is convenient if you have different demographicsor concepts that you have mapped on separate layers and want reported on separate sheets.
- Click Go
The Excel file will show about ten rows, and will then start to scroll the window to keep pace with the data. You can theoretically match up to 65,534 locations against as many locations as can be mapped on a single MapPoint layer, but memory and time constraints make that impractical. For a distribution list of 100 locations, it takes about a second per facility to find the closest on a 1GHz machine.
