matching upc numbers and prices

[fitzsociety]

I am working on matching upc numbers from our vendor with our companies part
numbers. I have started working on this for a few months but half way the
new prices for 2008 have been downloaded and I have no idea how to take the
prices from another worksheet and match them to the worksheet I have now.
Each price can be referenced with a unique upc number.

 

[JLatham]

Look at the worksheet function VLOOKUP(). I believe you may be able to use
that to match old and new by UPC number and bring over the new pricing.

Tell us a little more about how the sheets are laid out (what columns the
UPC codes are in, and what columns the price(s) are in) and we may be able to
be more specific. Sheet names involved would be good to know also, since
you're apparently talking about 2 workbooks.

 

[fitzsociety]

I have upc numbers located in column A1:A7775 that are all unique to a
specific price on the same worksheet. Worksheet is named,
CLIENT_DISQUETTE_PRIX__IFC. The prices for these unique upc numbers are
located in column M1:M7775 of the same worksheet with each price
corresponding to one upc number. I am trying to bring prices from a
worksheet within the same file named IFC PRICE 2008. This worksheet also has
unique upc number in column A and Prices in column M. I was hoping that
excel can use the upc number from the worksheet, CLIENT_DISQUETTE_PRIX__IFC
and match them to the upc numbers from the worksheet, IFC PRICE 2008 and only
bring the price over, and place the price into column M matching it to the
unique upc number. PLEASE HELP!!

 

[JLatham]

Try this formula in cell M1 on the CLIENT_DISQUETTE_PRIX__IFC sheet:

=VLOOKUP(A1,'IFC PRICE 2008'!A$1:M$7775,13,FALSE)

Fill that formula down column M to row 7775 and your costs from the other
sheet should appear there - if the UPC code on this sheet is not on the IFC
PRICE 2008 sheet, you wll get a #N/A error in a cell in column M.

If there are values in column N down to row 7775 of the CLIENT... sheet, you
can quickly fill the formula down the sheet by moving the cursor to the lower
right corner of M1 until it turns into a thin plus sign (+) instead of its
normal fat cross and then double-click the left mouse button.

Another way, select M1 and then in the Excel 'Name Box' (where you see the
indication that you've chosen cell M1 just above column A on the sheet) and
type in M1:M7775 and then choose Edit | Fill | Down.

Hope this helps you out.

 

[fitzsociety]

This worked like a champ. There is one more issue. Some of the upc numbers
are being used for different parts, so the prices came over but do not match
with the changed upc number. Do you think it is possible to also have it
look for other identifiers such as the diameter and length. The diameter is
located in column g and the length is located in column h. that way if these
dont match it will show n/a. Then i will go through the n/a. I appreciate
any help.

 

[JLatham]

So the rule(s) would be something like this/these:

If a UPC code appears to be a match, both the Length and Width of the
matched item against the length and width of the item we are attempting to
bring the price over to; if either the length or width is different, then
don't bring the cost over even though the UPCs matched.

It'll be a bit trickier, but should be able to do it. I think it can be
done in a single formula, complex perhaps, in the column, or if that doesn't
pan out, then with the assistance of one 'helper' formula on the CLIENT...
sheet.

 

[fitzsociety]

That is what I would need to be sure that the numbers match with the parts
and the price. I appreciate your knowledge and help.

 

[JLatham]

Alright then, put this formula into M1 on the CLIENT... sheet just as you did
the previous one. This time, the "move cursor to lower left corner of M1 and
double-click" should work to fill the formula down the sheet.

Yes, this is just one huge formula ---

=IF(G1=INDEX('IFC PRICE 2008'!G$1:G$7775,MATCH(A1,'IFC PRICE
2008'!A$1:A$7775,0),0),IF(H1=INDEX('IFC PRICE 2008'!H$1:H$7775,MATCH(A1,'IFC
PRICE 2008'!A$1:A$7775,0),0),IF(ISNA(VLOOKUP(A1,'IFC PRICE
2008'!A$1:M$7775,13,FALSE)),"No UPC Match",VLOOKUP(A1,'IFC PRICE
2008'!A$1:M$7775,13,FALSE)),"Size (W) Mismatch"),"Size (L) Mismatch")

What it does is first make sure that the length values in column G of both
sheets match for the first MATCH on the current UPC code, and if that
matches, check for match of the width in column H, again for the current UPC
Code. If either of those does not match you'll get an indication of that.
If both Width and Length don't match, then you'll get the indicator that the
Length didn't match. It continues on to either return the matching price (if
length/width matched) or tell you that it couldn't find the current UPC code
over on the ...2008 sheet.

Now, for any 'error' indicators that you replace with a manually entered
price, you have to remember that the formula will get erased on the CLIENT...
sheet, column M in that/those cell(s). A cell can hold a formula or a value,
not both. We start with formulas, you may end up changing some to values.

 

[fitzsociety]

It keeps saying #N/A.
--
Mike Fitz

Alright then, put this formula into M1 on the CLIENT... sheet just as you did
the previous one. This time, the "move cursor to lower left corner of M1 and
double-click" should work to fill the formula down the sheet.

Yes, this is just one huge formula ---

=IF(G1=INDEX('IFC PRICE 2008'!G$1:G$7775,MATCH(A1,'IFC PRICE
2008'!A$1:A$7775,0),0),IF(H1=INDEX('IFC PRICE 2008'!H$1:H$7775,MATCH(A1,'IFC
PRICE 2008'!A$1:A$7775,0),0),IF(ISNA(VLOOKUP(A1,'IFC PRICE
2008'!A$1:M$7775,13,FALSE)),"No UPC Match",VLOOKUP(A1,'IFC PRICE
2008'!A$1:M$7775,13,FALSE)),"Size (W) Mismatch"),"Size (L) Mismatch")

What it does is first make sure that the length values in column G of both
sheets match for the first MATCH on the current UPC code, and if that
matches, check for match of the width in column H, again for the current UPC
Code. If either of those does not match you'll get an indication of that.
If both Width and Length don't match, then you'll get the indicator that the
Length didn't match. It continues on to either return the matching price (if
length/width matched) or tell you that it couldn't find the current UPC code
over on the ...2008 sheet.

Now, for any 'error' indicators that you replace with a manually entered
price, you have to remember that the formula will get erased on the CLIENT...
sheet, column M in that/those cell(s). A cell can hold a formula or a value,
not both. We start with formulas, you may end up changing some to values.

 

[fitzsociety]

Will letters in column H affect the function. Some sizes have MM at the end
of them.

 

[JLatham]

The MATCH() function that's in there depends on finding an exact match of the
UPC codes in both sheets. The part of the formula like IF(H1=INDEX(... is
also dependent on an exact match, so 99mm would not = 99. You'd have what
amounts to a type mismatch.

Is it possible for you to send the workbook as an email attachment to
(remove spaces)
Help From @ jlathamsite. com
??

 

[JLatham]

After all has been said and done, I found this formula to work very well for
your actual setup:

=IF(ISNA(MATCH(A2,'IFC PRICE 2008 NEW'!A:A,0)),"No UPC
Match",IF(G2=INDEX('IFC PRICE 2008 NEW'!G:G,MATCH(A2,'IFC PRICE 2008
NEW'!A:A,0),0),IF(H2=INDEX('IFC PRICE 2008 NEW'!H:H,MATCH(A2,'IFC PRICE 2008
NEW'!A:A,0),0),IF(ISNA(VLOOKUP(A2,'IFC PRICE 2008 NEW'!A:M,13,FALSE)),"No UPC
Match",VLOOKUP(A2,'IFC PRICE 2008 NEW'!A:M,13,FALSE)),"Size (W)
Mismatch"),"Size (L) Mismatch"))

That was placed in row 2 and filled to the end of your data area. The
workbook you sent me has been returned with the changes made along with an
explanation.