Convert an LTL Rate Matrix to a List

Overview

Most LTL rates are presented as a matrix, where each data value is based upon an origin state and a destination state. Contract Manager is unable to read this sort of file directly. Therefore, it is important to know how to convert a rate matrix into a list format that can be imported and used in Contract Manager.

Prerequisites

  • The carrier rate matrix must be in a Microsoft Excel spreadsheet format.
  • User must have a version of Excel that supports PivotTables (Excel 2003 or later.) These instructions assume Excel 2010.

Process

To Convert an LTL Rate Matrix to a List:

  1. Open the LTL rate matrix in Microsoft Excel.
  2. Press Alt + D. The Office Access Key prompt appears.
  3. Press P.

The PivotTable and PivotChart Wizard opens.

  1. Select Multiple consolidation ranges.
  2. Select PivotTable.
  3. Click Next.

  1. Select I will create the page fields.
  2. Click Next.

  1. Click the range selector by the Range field.
  2. Click and drag to select the entire rate matrix, including column and row headers. Exclude any Canadian states, since Contract Manager will not recognize them.

  1. Press Enter.
  2. Click Add.
  3. Click Next.

  1. Select New Worksheet.
  2. Click Finish.

A PivotTable version of the rate matrix is created in a new tab of the spreadsheet.

  1. Scroll to the very bottom and far right of the PivotTable.
  2. Double click the cell containing the total of the Grand Total column.

Result

The rate matrix appears in a list format that can be copied and pasted into the corresponding columns of the Contract Manager CSV file template. See Format a CSV File for LTL Tiers for information on the formatting of this template.

Related Tasks

Enter Tariff Rates

Reference Materials

Format a CSV File for LTL Tiers