Preparing a client supplied Excel Distribution List and Grid for aACE Shipment import
Copy Worksheet
Before manipulating a Client’s distribution instructions, always keep an untouched version, and work in a copy so that if any mistakes are made, reviewing the original data can catch them.
Right click the Original tab and select Move or Copy from the drop-down list. Select (move to end) and check “Create a Copy”, then click OK.
This will create a second Tab. Rename the original tab/worksheet “Original”.
Convert Quantity Grid to Value Grid
The most complicated part of preparing an import for Shipping can be assigning a Declared Value to each Destination. If all the addresses get the same package, this is much more simple, but if each address receives a different quantity of line items, this can be quite intimidating. But, taken in steps and with some care, this too can become a simple task.
In summary the steps are:
1. Edit the worksheet to get the address list on one axis of the Grid and the Items sent on the second axis
2. Assign Prices to Line Items
3. Clear extra information in the sheet
4. Create sister Value grid by multiplying Quantity Grid by prices
5. Sum Rows of the Value Grid to get a Total Value for each Address
Step 1. Get the address list on one axis of the Grid and the Items sent on the second axis.
Step 2. Assign Grid Items a line item value or set of line items value (below each column has a LI (line item) and a Price.
The price is picked up from the Order’s Line Item Unit Price.
If multiple line items make up a single deliverable, sum their Unit Prices to a single Price per deliverable
Step 3. Clear the extraneous information. It helps if you copy the worksheet to a new tab and then clean up the grid to only essential information
Step 4. The Unit Price is at the top of the Grid and Quantities populate the grid itself. Now we can create the Value Grid. On the first address row, next to the last Line Item column, enter a calculation that multiplies the Unit Price of Line Item 1 with, the cell below it, the Quantity in the first Address Row. (below, this calculation is =E3*E2) (* means multiply).
Step 4a. Repeat this for each cell in the 1st address row of the grid.
Step 4b. Fill in the rest of the grid.
Fix the Price cell in the calculation by typing an $ in front of the column letter and row number of the cell reference. Meaning, make the calculation =E3*E2 into =E3*$E$2
Repeat this for each of the first row’s cells.
Place the curser over the lower right corner of the cell. Click and drag to the last row of the grid. This will fill the calculation down with the Quantity of that row multiplied by the unit price.
Repeat for each col
Step 4c. Copy Value Grid from Worksheet to a new worksheet.
Make sure to Copy and Paste VALUES. This will replace the calculations with the actual numbers with no underlying equations. When pasting, right click and select Paste Special from the drop-down. Check the Values radial button. Click OK.
Step 5. The last step in creating the Value Grid is to sum the Rows (total value assigned to each address). In a new column enter an equation to sum the values of the Line Items for that Address. Once done for the first row, place the curser over the lower right corner of the cell. Click and drag to the last row of the grid. This will fill the calculation down with the sums of each row populating the column. You now have a Value to assign to each shipment.
By adding this value to the import file, you can apply appropriate sales tax without having to populate the Packing List with exact items.
Format supplied Addresses to fit the import template.
The template is set up in the below format.
The primary task in organizing a supplied address is to break out the City, State and Zip code into separate columns. It also may be necessary to break up the street address into separate columns. There is no magic way to do this. An amount of manual work is necessary, but there is an easier way than cutting and pasting each address one at a time. This is the Text to Columns button in the Data tab. This command will separate the text within one column into multiple columns. The command has several options on HOW exactly to break up the text. The simplest for addresses is to insert a special character (I like to use the “/ “ ) into the addresses where you want a break to occur.
The separated columns will fill to the right of the right of the target column by default, so first make sure those columns are blank. Moving the address column to the far right of the data before running “Text to Columns” is a good practice.
Highlight the column you with to separate, then click the Text to Columns button in the Data tab.
The Convert Text to Columns Wizard appears. Select the radial button for Delimited, then the Next button.
In the next window, check the Other checkbox. Enter the character with which you wish to delimit (separate) the columns. Click the Next button.
Lastly, review the way the system will separate the text in the Data Preview window. Click Finish.
The “/” is removed from the text and replaced with a column separation. Note, below, if a cell has not delimiter (/}, then it is not changed.
Review the separated addresses against the original to make sure no unintended events occurred.
Conform Zip Code Column
Once the zip code is in its own column, the next step in getting the file ready for import is to format the Zip code column.
Highlight the zip code column, click into the Home tab, select Custom from the Number drop-down list.
A Format Cells window appears. Select Special in the left side window, then Zip Code in the Right side window. Click OK.
This will allow for zip codes that start with a zero to show the zero. Normal number formats will drop the zero.
Sort the list by zip code. Identify any international addresses and confirm they are still intact.
The last step is to copy the addresses and values into an IMPORT worksheet following the template set up, and containing the template headers in the first row. Save the file.
It may be desired to save international addresses to a second IMPORT worksheet, since normally they will be shipped using a different courier service. Save the file.
See Distribution Lists for instructions on the Import (duplicate to import) process.
Special Considerations
It may happen that Line Item charges in the Order are additional to Line Items in the distribution. To appropriately allocate those charges to the delivery addresses (and therefore tax them appropriately), the charges should be added into the grid’s unit price for the associated Line Items.
In the example below: we should add $16 to Unit Prices for the three related lines because the dowels are additional elements of those deliveries.
It may happen that Line Item charges in the Order are additional to each address in the distribution. To appropriately allocate those charges to the delivery addresses (and therefore tax them appropriately), the charges should be added into the grid’s summed price for each address.
In the example below: we should add $22 to each Address for P&H charges.
Some charges are not part of a particular deliverable.
In the example below: $450 line item for Prepress Prep is not assigned to a Delivery Address at all. PDF’s were sent out to client, so the default NY tax on electronic delivery is assigned based on the Order’s ShipTo address (this represents the assumed delivery address for the electronic delivery).