Merge 2 unrelated workbooks

[Guest]

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!

 

[Guest]

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,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,MATCH(A4,'Spreadsheet B'!A:A,0)))

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

Hope that helps,

TK

 

[stevebriz]

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

 

[Guest]

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!