Hard to explain, but here it goes:
Spreadsheet A has 4 columns:
Part #, Description, Current Cost, 2007 Cost
In the part number column there is 4000 parts. We only make 1500 of those
(it's a Customers Spreadsheet). All the fields are filled in except 2007
Cost. They want our parts 2007 price put in there.

Spreadsheet B has our data... 4 columns
Part #, Description, Current Cost, 2007 Cost.
1500 parts. Everything filled in, including 2007 Cost.

I want Spreadsheet A to say, if Spreadsheet B has a part number that matches
A, pull the 2007 cost from spreadhseet B and put it in spreadsheet A 2007
cost field.

So in theory, A would update the parts price from spreadsheet B.

Hope this isn't confusing... example:

SpreadSheet A

Part Desc 06 Price 07 Price
1 block 5.00
2 sphere 7.00


Spreadsheet B

Part Desc 06 Price 07 Price
1 blk blk 4.50 6.50
2 rd crcl 6.50 9.50


We have different desc, different 06 price (it's our sell price compared to
theirs), however the part number is the same. However they might have part
number 3 and we don't. The one's they have that we have I want the 2007 price
to fill in their sheet.

Thank you in advance!

You can use a combination of the INDEX() and MATCH() functions to populate
the price column in Sheet A. Try entering something like the following in
cell D2 of Spreadsheet A:

=INDEX('Spreadsheet B'!D:D,MATCH(A2,'Spreadsheet B'!A:A,0))

Then copy this down the entire column. This will update the 07 Price values
where there is a match, but non-matching values will show a #N/A error. If
you want to avoid these errors try a formula like:

=IF(ISERROR(MATCH(A4,'Spreadsheet B'!A:A,0)),"",INDEX('Spreadsheet
B'!D:D,MATCH(A4,'Spreadsheet B'!A:A,0)))

(watch out for page breaks that web posting puts in the formula)

Hope that helps,

TK
"Jerid B" wrote:

Hard to explain, but here it goes:
Spreadsheet A has 4 columns:
Part #, Description, Current Cost, 2007 Cost
In the part number column there is 4000 parts. We only make 1500 of those
(it's a Customers Spreadsheet). All the fields are filled in except 2007
Cost. They want our parts 2007 price put in there.

Spreadsheet B has our data... 4 columns
Part #, Description, Current Cost, 2007 Cost.
1500 parts. Everything filled in, including 2007 Cost.

I want Spreadsheet A to say, if Spreadsheet B has a part number that matches
A, pull the 2007 cost from spreadhseet B and put it in spreadsheet A 2007
cost field.

So in theory, A would update the parts price from spreadsheet B.

Hope this isn't confusing... example:

SpreadSheet A

Part Desc 06 Price 07 Price
1 block 5.00
2 sphere 7.00


Spreadsheet B

Part Desc 06 Price 07 Price
1 blk blk 4.50 6.50
2 rd crcl 6.50 9.50


We have different desc, different 06 price (it's our sell price compared to
theirs), however the part number is the same. However they might have part
number 3 and we don't. The one's they have that we have I want the 2007 price
to fill in their sheet.

Thank you in advance!

try this.
run this macro
If you need help on how to do this let me know


sub macro1()

Sheet1.Select
Dim i As Integer
Dim cost2007 As Variant
For i = 1 To 4000 ' Number of rows on Sheet A

cost2007 = ""
With Sheet2.Range("A1:A1500") ' Range on you sheet B
'MsgBox Sheet1.Cells(i, 1).Value
Set c = .Find(Sheet1.Cells(i, 1).Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
cost2007 = Sheet2.Cells(c.Row, 4).Value
' MsgBox c.Address & " " & Sheet2.Cells(c.Row, 4).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Sheet1.Cells(i, 4).Value = cost2007
Next i
End Sub

Jerid B wrote:
Hard to explain, but here it goes:
Spreadsheet A has 4 columns:
Part #, Description, Current Cost, 2007 Cost
In the part number column there is 4000 parts. We only make 1500 of those
(it's a Customers Spreadsheet). All the fields are filled in except 2007
Cost. They want our parts 2007 price put in there.

Spreadsheet B has our data... 4 columns
Part #, Description, Current Cost, 2007 Cost.
1500 parts. Everything filled in, including 2007 Cost.

I want Spreadsheet A to say, if Spreadsheet B has a part number that matches
A, pull the 2007 cost from spreadhseet B and put it in spreadsheet A 2007
cost field.

So in theory, A would update the parts price from spreadsheet B.

Hope this isn't confusing... example:

SpreadSheet A

Part Desc 06 Price 07 Price
1 block 5.00
2 sphere 7.00


Spreadsheet B

Part Desc 06 Price 07 Price
1 blk blk 4.50 6.50
2 rd crcl 6.50 9.50


We have different desc, different 06 price (it's our sell price compared to
theirs), however the part number is the same. However they might have part
number 3 and we don't. The one's they have that we have I want the 2007 price
to fill in their sheet.

Thank you in advance!

Thank you both, but I figured it out.
The Vlookup function worked out great.
It's been one of those weeks where everything is hitting me late!

"Jerid B" wrote:

Hard to explain, but here it goes:
Spreadsheet A has 4 columns:
Part #, Description, Current Cost, 2007 Cost
In the part number column there is 4000 parts. We only make 1500 of those
(it's a Customers Spreadsheet). All the fields are filled in except 2007
Cost. They want our parts 2007 price put in there.

Spreadsheet B has our data... 4 columns
Part #, Description, Current Cost, 2007 Cost.
1500 parts. Everything filled in, including 2007 Cost.

I want Spreadsheet A to say, if Spreadsheet B has a part number that matches
A, pull the 2007 cost from spreadhseet B and put it in spreadsheet A 2007
cost field.

So in theory, A would update the parts price from spreadsheet B.

Hope this isn't confusing... example:

SpreadSheet A

Part Desc 06 Price 07 Price
1 block 5.00
2 sphere 7.00


Spreadsheet B

Part Desc 06 Price 07 Price
1 blk blk 4.50 6.50
2 rd crcl 6.50 9.50


We have different desc, different 06 price (it's our sell price compared to
theirs), however the part number is the same. However they might have part
number 3 and we don't. The one's they have that we have I want the 2007 price
to fill in their sheet.

Thank you in advance!