Steps to Customize / Add Parts
1) Download these two files and save them to a directory on your hard drive.Material List (material-list.xlsx)
Part Numbers (parts.xlsx)
2) Open parts.xlsx to review its contents (Shown below)
Column A contains the 'description' field from the Contractor material list
Column B will contain the part number you would like to match to this description
Try It
Copy and paste a description from material-list.xlsx to column A on parts.xlsx, then enter a part number in column B next to it.
3) Sort parts.xlsx by Column A 'ascending'
Hold the Control key down and click the column A and B headers to highlight both columns.
Click 'Data' on the top menu bar and then click 'Sort'.
Sort by column A ascending (from a-z)
4) Save parts.xlsx
That will transfer the new part number to the material list. The number will not transfer if parts.xls is not sorted by column A ascending.
The reason this works is that column A in material-list.xlsx contains a vlookup function linking both of the spreadsheets together. The parts.xlsx file must be in the same directory as the material list you are working on.
For each material list produced, simply copy the vlookup function (column C row 2) of parts.xlsx to each line on your material list.
The existing vlookup function is defined for a maximum of 200 part numbers. Here is how you can increase it to 500 lines or any number required depending on the size of your part database.
=VLOOKUP(B14,Sheet1!$A$1:$B$200,2,FALSE)
=VLOOKUP(B14,Sheet1!$A$1:$B$500,2,FALSE)
(Simply change $B$200 to $B$500)