Linkage data between two spreadsheet
 

hi all!

Can anyone help to solve the follow problem:

Spreadsheet A has the follow column:
-PartNo
-VendorCode
-Quantity

Spreadsheet B has
-PartNo
-PartName
-Unit Price

PartNo in both table are related and unique.
There are thousand of data in each spreadsheet. It is impossible to use
the if statement to join two tables, Also.. I understand it can use
Access to do it very easily, but I do not want to use Access... can I
do it in Excel?
Pls help...

How can I join these two tables into one table, has all those
information, as:
New spreadsheet
-PartNo
-VendorCode
-Quantity
-PartName
-Unit Price

Thank you very much for any suggestion.


--
vitality
------------------------------------------------------------------------
vitality's Profile: http://www.excelforum.com/member.php...o&userid=27267
View this thread: http://www.excelforum.com/showthread...hreadid=467742

vitality Wrote:
hi all!

Can anyone help to solve the follow problem:

Spreadsheet A has the follow column:
-PartNo
-VendorCode
-Quantity

Spreadsheet B has
-PartNo
-PartName
-Unit Price

PartNo in both table are related and unique.
There are thousand of data in each spreadsheet. It is impossible to use
the if statement to join two tables, Also.. I understand it can use
Access to do it very easily, but I do not want to use Access... can I
do it in Excel?
Pls help...

How can I join these two tables into one table, has all those
information, as:
New spreadsheet
-PartNo
-VendorCode
-Quantity
-PartName
-Unit Price

Thank you very much for any suggestion.

Hi Vitality

Use the VLOOKUP Function

Assuming data in spreadsheet A is columns A-C, make column D Part Name
and Column E Quantity

In cell D2 enter this formula

=VLOOKUP(A2,[Spreadsheet B]Sheet1!$A$1:$C$1000,2,0)

A2 is the cell reference of the Part No in Spreadsheet B, [Spreadsheet
B]Sheet 1 is the actual name of Spreadsheet B and the Sheet Name where
the data is stored, $A$1:$C$1000 is the range for your data, 2 is the
column number counting from the left that contains the Part Name, and 0
is to avoid problems with similar part numbers

You need to change the formula to suit your data and then copy down
column D

In Column E2 enter this formula

=VLOOKUP(A2,[Spreadsheet B]Sheet1!$A$1:$C$1000,3,0)

You need to change the formula to suit your data and then copy down
column E


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=467742

One way is to use VLOOKUP

Assuming the tables in sheets A & B
are in cols A to C, data from row2 down

In sheet: A
-----------
Paste the lables into D1:E1 : PartName, UnitPrice

Put in D2:
=VLOOKUP($A2,B!$A:$C,COLUMNS($A$1:A1)+1,0)

Copy D2 across to E2, fill down as far as required
Format col E as currency

The above will bring over the PartName & UnitPrice
data from sheet B's cols B and C into sheet A's cols D and E
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"vitality" wrote in
message ...

hi all!

Can anyone help to solve the follow problem:

Spreadsheet A has the follow column:
-PartNo
-VendorCode
-Quantity

Spreadsheet B has
-PartNo
-PartName
-Unit Price

PartNo in both table are related and unique.
There are thousand of data in each spreadsheet. It is impossible to use
the if statement to join two tables, Also.. I understand it can use
Access to do it very easily, but I do not want to use Access... can I
do it in Excel?
Pls help...

How can I join these two tables into one table, has all those
information, as:
New spreadsheet
-PartNo
-VendorCode
-Quantity
-PartName
-Unit Price

Thank you very much for any suggestion.


--
vitality
------------------------------------------------------------------------
vitality's Profile:
http://www.excelforum.com/member.php...o&userid=27267
View this thread: http://www.excelforum.com/showthread...hreadid=467742