Add Part Numbers to Material List

Technology • Performance • Service

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)

parts.xls file contents

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)

Create Your Account Now!

Get 30 days trial. No credit card required.

SIGN UP