Thursday, 15 January 2015

Excel ipart linking

I want to share a couple of excel tricks for retrieving data from other spreadsheets with vlookup match and error handling like when reporting #NA. We have a single excel database that contains all our part numbers, prices, description, vendor and some more info. What I wanted was to link each member in our ipart table (eventually a CC item) to our price parts list and ask excel to find our part number, place it in stock number and find the price and place it in cost center.
For simplicity I have copied the price list spreadsheet to a local path next o our library folder where we have all the components we author. This way I can work on it at home and in places where the mapped network drive is not accessible. I copy the new price list each year when it changes. I only used the price in the cost center of each item to provide a default value instead of leaving it blank.
What we intend to do is export the BOM and use the same vlookup formula to find the latest prices before doing a quote.  The price we put in CC is so we can have a value / any value, rather than leaving it blank bu we intend to update the BOM export with the vlookup formula at every export.
The tricky part was finding cells based on partial data. I have the manufacturer code, Georg Fisher part number, but in our price parts list we don’t have a cell with just GF Part number instead it holds something like this: “GF REF: 729101108” or “GF REF: 729101107  DRAIN CENTRE REF. P31127”. So all I have is 729101108 and I need to use a formula that can find a match inside a larger text and return the part number and price.



Can’t use left, right, mid, and len functions because Part Number appears randomly depending on who entered this info.
The solution is to use MATCH to find part of a text like *729101108* (* used as wildcard) and INDEX to get our Part Number value.
Once I found our part number for that item I can simply use VLOOKUP to get the price since the part number is a single unique cell value.
Here is the formula to get our Part Number (we store it in the parts Stock Number ipropertie).
=IF(ISNA(INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0))),"",INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0)))
Sounds complicated? Let’s brake it into simple terms.
What we’re saying is IF the value is not found (excel returns NA) then don’t put anything in the cell (“”)and if found then put the value.
=IF(ISNA(INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0))),"",INDEX('[Price List.xls]Parts Price List'!$A$1:$A$15000,MATCH(("*"&U5&"*"),'[Price List.xls]Parts Price List'!$E:$E,0)))
MATCH(("*"&U5&"*") will actually try and find the value in cell U5 (our number 729101108 with anything before or after it in any part of the text of column E in our price list. Here is the microsoft help page.
ISNA function on microsoft’s webpage
Index is used to retrieve the value of our company’s Part Number (A row) inside our price list. Here is the microsoft help page.
The price (cost center) is much simpler.
=IF(M2="","","£"&(VLOOKUP(M2,'[Price List.xls]Parts Price List'!$A:$F,3,FALSE)))
If there is no part number (stock number) in cell M then don’t put anything but if there is a part number then add £ in front and then the value of column 3 in our price list.
Here is the VLOOKUP on microsoft webpage.
http://office.microsoft.com/en-gb/excel-help/vlookup-function-HP010343011.aspx?CTT=1
                Some experts say that the IDEX MATCH is more powerfull and faster than VLOOKUP so if possible use it at all times.
                The best explanation on how to use it and comparasson with VLOOKUP I found here:
and a couple of reasons why VLOOKUP might not work

I have attached my Georg Fischer iPart excel template for your review. I am sure it’s better to see all this at work rather than trying to understand my explanation.

Here is the excel file.

Later,
ADS