Creating Vendor Cost Lists Follow

Overview

This document covers converting the three major glass vendor price list files to a format we can import into GlasPacLX for vendor costs.

Creating the Datafile

Vendor Costs are loaded into Vendor Maintenance form (Purchasing > Vendor Maintenance > Edit Vendor) and is only available when the customer has ICPO. Without ICPO the Edi Vendor Cost button will not be shown.

Contents

  • EXCEL Formulas reference
  • MYGRANT
  • PGW ALT FORMAT - Customer Price List
  • PILKINGTON

The EXCEL FORMULAS

Quick view of the EXCEL formulas –

Mygrant (Column C)

=LEFT(A1,7)&MID(A1,9,3)&IF(MID(A1,8,1)="P","Y","N")&RIGHT(A1,3)

PGW Customer Price List (Column D)

=LEFT(A2,7) & MID(A2,9,3) & IF(B2="PRE", "Y", "N") & IF(B2="PRE", "", B2)

Pilkington (Column D)

=LEFT(B2,7) & MID(B2,9,3) & IF(MID(B2,8,1)="P", "Y","N") & IF(LEN(B2)=14,RIGHT(B2,3),"")

Vendor Cost List: Mygrant

Overview

  1. Format Part Number to confirm to 12-digit NAGS w/trademark format
  2. Remove non-NAGS parts
  3. Save as a .CSV file.
  4. Load into Vendor Maintenance
  5. Understanding exceptions

Step 1 – Format Part Number

Mygrant provides the part number in a base part + Premium indication + color/attachment group + trademark format. There is space between the base part, color group, trademark on most lines.

Here’s a detail of the format –

Position 1-7 – Base Part, example DB00003

Position 8 – Premium Part indication, “P”, or space for a non-premium part

Position 9-11 – Color & Attachment group, example CLN

Position 12 – space

Position 13-15 – Manufacturer trademark, example LOF

Here is a sample of some Domestic Backglass items from a Mygrant price file in Excel.
Note: Some of lines contain non-NAGS part numbers (36, 37, and 38). These items cannot be loaded into the vendor cost list.

Here’s the formula to use to format the Mygrant part line.

=LEFT(A1,7)&MID(A1,9,3)&IF(MID(A1,8,1)="P","Y","N")&RIGHT(A1,3)

  1. Open the .csv file from Mygrant in Excel.
  2. Click in Column C in line 1.
  3. Paste the above formula into the function box for C1.
  4. Copy the formula for the run of the document by double clicking on the small black dot in the lower right corner of cell C1.  
                    
  5. Copy all of Column C
  6. Select Column A.
  7. Right click at the top of Column A and select Paste – Values (the clipboard icon with 123 on it). If you are using an older version of Excel, select Paste Special and select Values Only.
  8. Delete Column C. Right click on the C label at the top and select Delete.

Remove Non-NAGS Parts

There’s a number of Non-NAGS Parts in the Mygrant price file. These items that aren’t standard 12 character NAGS part numbers. Anything that does not begin with the letter D or letter F is non-NAGS.

  1. Review the file for groups of parts to remove. For example here, the first 38-lines of this price file are non-NAGS parts.
  2. Select the entire lines. Right-click and select “Delete”
  3. There’s a few parts between the Domestic and Foreign parts.
  4. As well as number items at the end of the list

Save the File as a CSV

You should now have a file that looks like this at the top.

  1. Select File, Save As…, and save the file with a new name in .CSV format.
  2. Click Yes to the subsequent dialog to confirm the save.

Have more questions? Submit a request

Comments

Powered by Zendesk