can i use vlookup for this??

  • Thread starter Thread starter Roger Dodger
  • Start date Start date
R

Roger Dodger

Hi everyone,

I am trying to bring information from one workbook to another workbook.

Workbook 1 has a list of numbers that represent corresponding chemicals we
use, beside them Column B is the actual name of the chemical.

Workbook 2 has all the information about the chemical without the name.
Column A is the number as in workbook 1.

What I want to do is bring workbook 2 info across to workbook 1 so that I
get the chemical number, chemical name and the corresponding data in one
workbook. One possible problem is that while they are sorted by chemical
number, some chemicals will be different. Workbook 1 has all the ones that I
need so it would be good to use it as my final workbook.


Also I am not a pro at this so it needs to be explained to me in simple
terms.

If someone can help I would be very grateful.


Roger
 
Yes, you can use VLOOKUP for this. You don't say how many columns of
information you want to bring across, so can you give a quick example
of what you have in Workbook 2, and where you want this to appear in
workbook 1?

Pete
 
Pete,

Workbook 2 has 13 colums of data.
Col A - Chemical number, Col B product description, Col C- safety stock, Col
4- General stock. etc.etc
Column B info I don't want but i can live with it if i have to.

In workbook 1 i have Col A Chemical number, Col B - Chemical name, Col C
supplier.

I want workbook 1 to match up the chemical number in workbook 2 and add the
info into workbook 1.

Then i will have chemical number,chemical name, from workbook 1
and
Safety stock,general stock, finishing stock,Coating stock,dyeing
stock,printing stock,Active product. consignment product,locked product,last
used date,Total allocated stock.

I hope that this makes sense to you. If not i can send you the 2 workbooks
if you like but they might be just as confusing.

Thanks so much for helping

Roger


Yes, you can use VLOOKUP for this. You don't say how many columns of
information you want to bring across, so can you give a quick example
of what you have in Workbook 2, and where you want this to appear in
workbook 1?

Pete
 
I hope that your First Row of Workbook1 is having the Column headers
(i.e.) Chemical number, chemical name, supplier like that and your
data start from 2nd Row.

Open your Workbook1 and after that open the Workbook2, now click the
Workbook1
in the taskbar and in B2 cell copy and paste the below formula.

=VLOOKUP($A2,[Workbook2.xls]Sheet1!$A:$N,COLUMN(B1),0)

Drag the B2 cell to the right 13 columns and down rows as you require.

You didn’t mention your Exact Name of your Workbook2, if it is
Workbook2 then use the formula as it is. If your name of the
workbook2 is different then just change the word Workbook2 in the
above formula to your file name.

At the same like the workbook2 you didn’t mention the sheet name of
the workbook2 on which the data is present. So change the Sheet1 to
your data sheet name in the above formula, if required.

If your Workbook1 column-A Chemical code is not present in Column-A of
sheet1-Workbook2 then it will result #N/A error. To show those #N/A
cells as blank, then try the below instead of the above formula.

=IF(ISNA(VLOOKUP($A2,[Workbook2.xls]Sheet1!$A:$N,COLUMN(B1),
0)),"",VLOOKUP($A2,[Workbook2.xls]Sheet1!$A:$N,COLUMN(B1),0))

Hope it’s clear!
 
Okay, assuming you have row 1 as a header row in both workbooks, it
will be easier to construct the formula with both workbooks open (you
can close workbook2 later). Start to enter this formula in C2 of
workbook1:

=VLOOKUP($A2,

at this point you should click on the window of workbook2 and then
highlight all the data that you want to encompass, eg from A2 to M500,
then continue typing as follows:

,COLUMN(C1),0)

and then press <enter>.

Excel will have completed the formula for you and it should look
something like this when viewed in the formula bar:

=VLOOKUP($A2,[workbook2.xls]Sheet1!$A$2:$M$500,COLUMN(C1),0)

Note that the name of workbook2 appears in square brackets, and you
might also have apostrophes around the filename and sheetname if you
have any spaces in them. This formula will bring the data from column
C (safety stock) that matches the entry in A2.

You can now close workbook2, and you should still see the value in C2.
However, the formula will now have been expanded to include the full
path to workbook2, so this method makes it easy for you to get the
correct syntax.

You can then copy the formula across to M2, and you should see all the
data that you require for that Chemical Number. The formulae in C2:M2
can then be copied down for as many chemicals as you have in column A.

You might like to save this workbook with a different name, so that
you still have workbook1 and workbook2 if you want to work with them
separately.

If this is static data, you might like to fix the values of those
formulae. Highlight columns C to M then click <copy>, then right-click
and select Paste Special, and then click on Values and OK, then press
<Esc>.

Hope this helps.

Pete
 
Hi Roger

Unless I have misunderstood you, it seems to me there is less work to do if
you carry out the task the other way around.
Insert a New column at B in Workbook2
In cell B2 enter
=IF(A2="","",VLOOKUP(A2,[Workbook1.xls]Sheet1!A:B,2,0)
Copy down as required

--

Regards
Roger Govier

Roger Dodger said:
Pete,

Workbook 2 has 13 colums of data.
Col A - Chemical number, Col B product description, Col C- safety stock,
Col 4- General stock. etc.etc
Column B info I don't want but i can live with it if i have to.

In workbook 1 i have Col A Chemical number, Col B - Chemical name, Col C
supplier.

I want workbook 1 to match up the chemical number in workbook 2 and add
the info into workbook 1.

Then i will have chemical number,chemical name, from workbook 1
and
Safety stock,general stock, finishing stock,Coating stock,dyeing
stock,printing stock,Active product. consignment product,locked
product,last used date,Total allocated stock.

I hope that this makes sense to you. If not i can send you the 2 workbooks
if you like but they might be just as confusing.

Thanks so much for helping

Roger


Yes, you can use VLOOKUP for this. You don't say how many columns of
information you want to bring across, so can you give a quick example
of what you have in Workbook 2, and where you want this to appear in
workbook 1?

Pete





__________ Information from ESET Smart Security, version of virus
signature database 5263 (20100708) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5263 (20100708) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Thanks heaps everyone. I have it working now (thanks to you guys) You can't
believe how much easier my life will be from now on. Can anyone recommend a
book on excel? I have completed an intermediate course in excel but still
feel as though I am a dill. I have the dummies guide to excel as well as the
test book from my course but I still feel as though I understand about half
of one percent of what excel can do.

Thanks again

Kevin
Roger Govier said:
Hi Roger

Unless I have misunderstood you, it seems to me there is less work to do
if you carry out the task the other way around.
Insert a New column at B in Workbook2
In cell B2 enter
=IF(A2="","",VLOOKUP(A2,[Workbook1.xls]Sheet1!A:B,2,0)
Copy down as required

--

Regards
Roger Govier

Roger Dodger said:
Pete,

Workbook 2 has 13 colums of data.
Col A - Chemical number, Col B product description, Col C- safety stock,
Col 4- General stock. etc.etc
Column B info I don't want but i can live with it if i have to.

In workbook 1 i have Col A Chemical number, Col B - Chemical name, Col C
supplier.

I want workbook 1 to match up the chemical number in workbook 2 and add
the info into workbook 1.

Then i will have chemical number,chemical name, from workbook 1
and
Safety stock,general stock, finishing stock,Coating stock,dyeing
stock,printing stock,Active product. consignment product,locked
product,last used date,Total allocated stock.

I hope that this makes sense to you. If not i can send you the 2
workbooks if you like but they might be just as confusing.

Thanks so much for helping

Roger


Yes, you can use VLOOKUP for this. You don't say how many columns of
information you want to bring across, so can you give a quick example
of what you have in Workbook 2, and where you want this to appear in
workbook 1?

Pete





__________ Information from ESET Smart Security, version of virus
signature database 5263 (20100708) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus
signature database 5263 (20100708) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...
 
Back
Top