Merge data from 2 excel spreadsheets

[jake007]

Ok, I have 2 excel spreadsheets:

The first one looks like this:

Part_No Desc Type DealerCost List


Second one looks like this:

Part_No Desc Type


The first table has many more parts than the second. The second obviously has no costs in it. What I need to do is create/update the 2 cost columns in table 2 with the appropriate costs from table 1 based on the part number.

Any ideas?

Jake

 

[AnotherHiggins]

Is there more than one Desc or Type for any given Part_No?

Assuming there's not, then a simple VLookup should do it for you.

If your data is on Sheet1, A1:E10 and Sheet2, A1:C10, then on Sheet2, Cell D2 (under Dealer Cost), you'd put the following:
[tab]=VLookup(A2,Sheet1!$A$2:$E$10,4,0)

And in E2 (under List), you'd use this:
[tab]=VLookup(A2,Sheet1!$A$2:$E$10,5,0)



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jake007]

Tried the VLookup, works if I hard code the cell value such as

=vlookup("701.0954",I1:M11800,4,False) but not if I reference the cell such as

=vlookup(A2,I1:M11800,4,False)

????

Jake

 

[AnotherHiggins]

Your data is stored as NUMBERS in cell A2 and as TEXT in I1:M11800. I know this because when you 'hard coded' the Part_No in, you used quotes. That tells Excel to treat it as TEXT, and it worked. When you use a cell reference it doesn't work because A2 contains a NUMBER, not TEXT.

Since these are part numbers and you will never to arithmetic with them, they should always be stored as TEXT.

But when you type in 701.0954, Excel assumes that you are typing in a number. You can get around this by first typing in a single quote ( ' ). That tells Excel that anything that follows is text.

If you already have a long list and don't want to insert single quotes before every number, then you can use a formula such as [COLOR=blue white]=Text(A2,"@")[/color] in another column. Copy this newly created column and Paste Special > Values over the original column A. You will be left with part numbers stored as TEXT (as they should be).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.