Excel help

[never_retreat]

Any excel wizards in the hose.
I need to update some pricing at work. Here is the situation.
I have a sheet with the existing data. part number, cost, retail.
I have another sheet with the new data. part number, cost, retail, and Upc. The part number is the unique identifier the other 3 need to overwrite the old data.
How do I do this? Oh and the new sheet may have part numbers that are not in my data base, and my data might contain some part numbers that are not on the new sheet.

[drewtam]

Quick way would be to use either a "vlookup" or "match" function to find the match and output the new data.

=vlookup(look up value -> old part number, table array -> the new data set, column of data to output)

If it can't find a match, it will output an error. You could nest the vlookup inside an "if" statement, so that an error from vlookup outputs the old data set.

Do you need to import the new part numbers into the old sheet too?

[never_retreat]

Quick way would be to use either a "vlookup" or "match" function to find the match and output the new data.

=vlookup(look up value -> old part number, table array -> the new data set, column of data to output)

If it can't find a match, it will output an error. You could nest the vlookup inside an "if" statement, so that an error from vlookup outputs the old data set.

Do you need to import the new part numbers into the old sheet too?

I could not figure out the vlookup. No I don't need the new part numbers added. But I do need the new column with the upc codes associated with the old part number.

So should I put all this data onto the same sheet?

[never_retreat]

How would the fourmula look if my sheet looked like this.

a1 Existing part number, b1 cost, c1 retail, d1 new part number, e1 new cost, f1 new retail, g1 upc

[Tallpine]

How big are the data sets?  If not too big, you could copypaste the new data below the old data*, change the text color of the new data, and then sort the whole thing by part number and manually delete duplicate old part numbers 

* or add a new column: "Old" or "New"


Otherwise, if it was me I would just export both files to delimited text, write a C routine to merge them, and then import the result back into excel 

[drewtam]

Put each of these results in a separate column, where ever you like.

I went to g100, if your parts list is longer, then use whatever row it stops at.

The "$" symbol allows you to fill down the formula easier. As it fills down, the a1 inside the function will automatically be changed to a2, to a3, etc. But the "$" will prevent the table reference cells from changing.
http://spreadsheets.about.com/od/excel2010/ss/2011-05-23-excel-2010-fill-down-fill-handle.htm

Row 1
this will pull in the new cost
h1
=vlookup(a1,$d$1:$g$100,2)

this will pull in the new retail
i1
=vlookup(a1,$d$1:$g$100,3)

this will pull in the new upc
j1
=vlookup(a1,$d$1:$g$100,4)


Row 2
new cost
h2
=vlookup(a2,$d$1:$g$100,2)

new retail
i2
=vlookup(a2,$d$1:$g$100,3)

new upc
j2
=vlookup(a2,$d$1:$g$100,4)

[never_retreat]

How big are the data sets?  If not too big, you could copypaste the new data below the old data*, change the text color of the new data, and then sort the whole thing by part number and manually delete duplicate old part numbers 

* or add a new column: "Old" or "New"


Otherwise, if it was me I would just export both files to delimited text, write a C routine to merge them, and then import the result back into excel 

Big 2-3000 lines.

Thanks drewtam I will try yours out tomorrow.

[never_retreat]

Put each of these results in a separate column, where ever you like.

I went to g100, if your parts list is longer, then use whatever row it stops at.

The "$" symbol allows you to fill down the formula easier. As it fills down, the a1 inside the function will automatically be changed to a2, to a3, etc. But the "$" will prevent the table reference cells from changing.
http://spreadsheets.about.com/od/excel2010/ss/2011-05-23-excel-2010-fill-down-fill-handle.htm

Row 1
this will pull in the new cost
h1
=vlookup(a1,$d$1:$g$100,2)

this will pull in the new retail
i1
=vlookup(a1,$d$1:$g$100,3)

this will pull in the new upc
j1
=vlookup(a1,$d$1:$g$100,4)


Row 2
new cost
h2
=vlookup(a2,$d$1:$g$100,2)

new retail
i2
=vlookup(a2,$d$1:$g$100,3)

new upc
j2
=vlookup(a2,$d$1:$g$100,4)

I think this is working, I was playing around with it and it working. I had to add a false to the end because it was trying to find some stuff.
I just screwed it up because of some rounding that took place on some un formated columns.

[drewtam]

I think this is working, I was playing around with it and it working. I had to add a false to the end because it was trying to find some stuff.
I just screwed it up because of some rounding that took place on some un formated columns.

Oh yeah, I forgot that it sometimes finds the first answer it thinks is close when the it is not in order. Adding the final option of 'false' should make it find the exact match.

[never_retreat]

Oh yeah, I forgot that it sometimes finds the first answer it thinks is close when the it is not in order. Adding the final option of 'false' should make it find the exact match.

Most the part numbers have a sequence.
A812-44-m
A812-44-L
A812-44-xl

stuff like that.