Addning info from diffrent workbooks using VBA

[rantz]

I'm working on my master thesis and I could really use some help.

I have two workbooks and I want to add information from workbook2 to
workbook1 by matching the order numbers in the different lists.

Workbook1 includes the following columns

Ordernr |Date| etc

Workbook2 includes the following columns

Ordernr| Customer | Quality | etc

I want to take the first cell in the ordernr-col from workbook1 and
search for that specific order number in the ordernr-col in woorkbook2.
When I find that number I want to take the information (Customer,
Quality) from that row in workbook2 and add this information to
workbook1. If the order number is not found in workbook2 it can be
indicated in workbook1 as blank.

I want to loop through all of order numbers in workbook1.

Perhaps this is a matter for access but I think It could be written in
excel with VBA. I'm not that good with VBA so I tried to write how I
think it could be done. Maybe someone can help me with translation or
point me in the right direction.

Do While IsEmpty(ActiveCell.Offset(1, 0)) = False

1. Read value (order number) from cell workbook1.A"currentRow"
2. Search workbook2, column A for order number and return row-number to
"foundrow"
3. Read value (Customer) from cell workbook2, B."foundrow" and write
value (customer) to workbook1, C."currentRow"
4. If order number not found, then write blank or "not found" to
workbook1, C."currentRow"

Loop

 

[Barb Reinhardt]

Take a look at the following functions:

VLOOKUP
MATCH
ISNA

Both workbooks will need to be open in order to have the functions work
properly. If you want to access a closed workbook, you'll also need

INDIRECT.EXT within MOREFUNC.XLL from
http://xcell05.free.fr/

 

[Dave Peterson]

I think that those three functions will work fine with open or closed
workbooks--as long as there's nothing combined with them that would break it
(like =indirect()).

 

[Dave Peterson]

I wouldn't use a macro. I'd just apply a bunch of formulas. (I guess if I had
to have a macro, I'd use the macro to add the formulas (and maybe convert to
values).

Open both workbooks (excel will adjust the formulas to point at the closed
workbook (if/when you close workbook2)).

But by having both workbooks open, the formulas will recalc faster and you'll
find building the formulas much easier.

In workbook1 (sheet1???), find an unused column (I'm gonna use E).
Put "Index" in E1 (header row).
in E2, start your formula by typing:
=match(a2,
then hit alt enter to go to workbook2 and point column A (just click on the
header).
You'll see something like:
=MATCH(A2,[workbook2.xls]Sheet1!$A:$A
then type ",0)" (without the quotes)
You'll end up with:
=MATCH(A2,[workbook2.xls]Sheet1!$A:$A,0)

Change A2 to the cell that contains the ordernr of sheet1 of workbook1.
Change $A:$A to the column that contains ordernr of sheet1 of workbook2.

Drag this down the column.

You'll end up with a bunch of numbers and a bunch of #N/A's.
The number is the number of the first row that matched in workbook2's sheet1.

Now if you want to bring back the value in column X of workbook2's sheet1, you
can put this in F2:

=IF(ISERROR($E2),"",INDEX([workbook2.xls]Sheet1!$X:$X,$E2))

If some of those cells to bring back are empty, you'll see 0's. If you want to
make them look empty:

=IF(ISERROR($E2),"",IF(INDEX([workbook2.xls]Sheet1!$X:$X,$E2)="","",
INDEX([workbook2.xls]Sheet1!$X:$X,$E2)))
(all one cell)

Drag to the right and down the columns. (Change the $x:$x to whatever columns
you want to bring back--both spots!)

And format the cells the way you want--money/time/date/etc.

If you close workbook2, excel will change your formulas to look like:

=MATCH(A2,'C:\My Documents\excel\[workbook2.xls]Sheet1'!$A:$A,0)

=IF(ISERROR($E2),"",
IF(INDEX('C:\My Documents\excel\[workbook2.xls]Sheet1'!$X:$X,$E2)="","",
INDEX('C:\My Documents\excel\[workbook2.xls]Sheet1'!$X:$X,$E2)))

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

(=vlookup() is very similar to =index(match()). You may want to read about it,
too.

========
And if you really wanted a macro, you could build these formulas with the macro
recorder turned on and then tweak that macro.

 

[rantz]

Thank you very much for your help! I think this will work!

Dave Peterson skrev:

I wouldn't use a macro. I'd just apply a bunch of formulas. (I guess if I had
to have a macro, I'd use the macro to add the formulas (and maybe convert to
values).

Open both workbooks (excel will adjust the formulas to point at the closed
workbook (if/when you close workbook2)).

But by having both workbooks open, the formulas will recalc faster and you'll
find building the formulas much easier.

In workbook1 (sheet1???), find an unused column (I'm gonna use E).
Put "Index" in E1 (header row).
in E2, start your formula by typing:
=match(a2,
then hit alt enter to go to workbook2 and point column A (just click on the
header).
You'll see something like:
=MATCH(A2,[workbook2.xls]Sheet1!$A:$A
then type ",0)" (without the quotes)
You'll end up with:
=MATCH(A2,[workbook2.xls]Sheet1!$A:$A,0)

Change A2 to the cell that contains the ordernr of sheet1 of workbook1.
Change $A:$A to the column that contains ordernr of sheet1 of workbook2.

Drag this down the column.

You'll end up with a bunch of numbers and a bunch of #N/A's.
The number is the number of the first row that matched in workbook2's sheet1.

Now if you want to bring back the value in column X of workbook2's sheet1, you
can put this in F2:

=IF(ISERROR($E2),"",INDEX([workbook2.xls]Sheet1!$X:$X,$E2))

If some of those cells to bring back are empty, you'll see 0's. If you want to
make them look empty:

=IF(ISERROR($E2),"",IF(INDEX([workbook2.xls]Sheet1!$X:$X,$E2)="","",
INDEX([workbook2.xls]Sheet1!$X:$X,$E2)))
(all one cell)

Drag to the right and down the columns. (Change the $x:$x to whatever columns
you want to bring back--both spots!)

And format the cells the way you want--money/time/date/etc.

If you close workbook2, excel will change your formulas to look like:

=MATCH(A2,'C:\My Documents\excel\[workbook2.xls]Sheet1'!$A:$A,0)

=IF(ISERROR($E2),"",
IF(INDEX('C:\My Documents\excel\[workbook2.xls]Sheet1'!$X:$X,$E2)="","",
INDEX('C:\My Documents\excel\[workbook2.xls]Sheet1'!$X:$X,$E2)))

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

(=vlookup() is very similar to =index(match()). You may want to read about it,
too.

========
And if you really wanted a macro, you could build these formulas with the macro
recorder turned on and then tweak that macro.

I'm working on my master thesis and I could really use some help.

I have two workbooks and I want to add information from workbook2 to
workbook1 by matching the order numbers in the different lists.

Workbook1 includes the following columns

Ordernr |Date| etc

Workbook2 includes the following columns

Ordernr| Customer | Quality | etc

I want to take the first cell in the ordernr-col from workbook1 and
search for that specific order number in the ordernr-col in woorkbook2.
When I find that number I want to take the information (Customer,
Quality) from that row in workbook2 and add this information to
workbook1. If the order number is not found in workbook2 it can be
indicated in workbook1 as blank.

I want to loop through all of order numbers in workbook1.

Perhaps this is a matter for access but I think It could be written in
excel with VBA. I'm not that good with VBA so I tried to write how I
think it could be done. Maybe someone can help me with translation or
point me in the right direction.

Do While IsEmpty(ActiveCell.Offset(1, 0)) = False

1. Read value (order number) from cell workbook1.A"currentRow"
2. Search workbook2, column A for order number and return row-number to
"foundrow"
3. Read value (Customer) from cell workbook2, B."foundrow" and write
value (customer) to workbook1, C."currentRow"
4. If order number not found, then write blank or "not found" to
workbook1, C."currentRow"

Loop