I have two workbooks with different information--and a different number of
records--but a common column that can be used as a key. Can the smaller
workbook be used, with the common keys, to look up rows in the second
workbook and either import the data in specified columns or replace the data
in specified columns in the shorter workbook with data in the specified
columns in the larger workbook? To be explicit, the two workbooks have
address data that needs to be replaced in one.

I will greatly appreciate any comments and assistnace for creating this merge.

/s/ Alan Auerbach

I don't have the code ready to post here right this minute. But I just
finished up helping someone who contacted me direct with something similar -
but they wanted the moves from one workbook to the other to happen
automatically, so the code is not in the form that could be tossed right into
a macro at the moment. If no one else responds very shortly, I'll modify the
code and put it up here and you can alter it to suit you needs. How many
columns of information need to be moved from each row from one book to the
other?

"Alan Auerbach" wrote:

I have two workbooks with different information--and a different number of
records--but a common column that can be used as a key. Can the smaller
workbook be used, with the common keys, to look up rows in the second
workbook and either import the data in specified columns or replace the data
in specified columns in the shorter workbook with data in the specified
columns in the larger workbook? To be explicit, the two workbooks have
address data that needs to be replaced in one.

I will greatly appreciate any comments and assistnace for creating this merge.

/s/ Alan Auerbach

Thank you so much for offering to assist me!

The keys for the shorter workbook are in column A and for the longer
workbook they are in column B, but there is no reason I cannot delete column
A which would put all the keys in column A. Once the original column A is
deleted, the address data in the longer workbook is in columns J, K, L, M and
N. The address data in the shorter workbook (to be replaced with information
from the longer workbook) is in columns F, G, H, I and J.

The goal is to use the keys in the shorter workbook to lookup the data in
the longer workbook and, for each key in the shorter workbook, to replace the
data in columns F, G, H, I and J with the data in the longer workbook located
in columns J, K, L, M and N.

"JLatham" wrote:

I don't have the code ready to post here right this minute. But I just
finished up helping someone who contacted me direct with something similar -
but they wanted the moves from one workbook to the other to happen
automatically, so the code is not in the form that could be tossed right into
a macro at the moment. If no one else responds very shortly, I'll modify the
code and put it up here and you can alter it to suit you needs. How many
columns of information need to be moved from each row from one book to the
other?

"Alan Auerbach" wrote:

I have two workbooks with different information--and a different number of
records--but a common column that can be used as a key. Can the smaller
workbook be used, with the common keys, to look up rows in the second
workbook and either import the data in specified columns or replace the data
in specified columns in the shorter workbook with data in the specified
columns in the larger workbook? To be explicit, the two workbooks have
address data that needs to be replaced in one.

I will greatly appreciate any comments and assistnace for creating this merge.

/s/ Alan Auerbach

No need to move things around, we'll set up column 'mapping' in the code.
This will allow it to work right from the outset as you have things set up
now, and will permit you to rearrange things in either book later, with just
a little code alteration to keep it working with any such new arrangement.
I'm off to work on this now - turns out the other user needs almost exactly
the same thing [didn't really want automatic updates in real time, so I'm
converting that code to 'on-demand' macro and can get double-duty-use out of
it :-) ]

"Alan Auerbach" wrote:

Thank you so much for offering to assist me!

The keys for the shorter workbook are in column A and for the longer
workbook they are in column B, but there is no reason I cannot delete column
A which would put all the keys in column A. Once the original column A is
deleted, the address data in the longer workbook is in columns J, K, L, M and
N. The address data in the shorter workbook (to be replaced with information
from the longer workbook) is in columns F, G, H, I and J.

The goal is to use the keys in the shorter workbook to lookup the data in
the longer workbook and, for each key in the shorter workbook, to replace the
data in columns F, G, H, I and J with the data in the longer workbook located
in columns J, K, L, M and N.

"JLatham" wrote:

I don't have the code ready to post here right this minute. But I just
finished up helping someone who contacted me direct with something similar -
but they wanted the moves from one workbook to the other to happen
automatically, so the code is not in the form that could be tossed right into
a macro at the moment. If no one else responds very shortly, I'll modify the
code and put it up here and you can alter it to suit you needs. How many
columns of information need to be moved from each row from one book to the
other?

"Alan Auerbach" wrote:

I have two workbooks with different information--and a different number of
records--but a common column that can be used as a key. Can the smaller
workbook be used, with the common keys, to look up rows in the second
workbook and either import the data in specified columns or replace the data
in specified columns in the shorter workbook with data in the specified
columns in the larger workbook? To be explicit, the two workbooks have
address data that needs to be replaced in one.

I will greatly appreciate any comments and assistnace for creating this merge.

/s/ Alan Auerbach

You are a saint! I do not know how to thank you. I would reciprocate, but I
know that my skills are far inferior to your own. :-)

Thank you!

"JLatham" wrote:

No need to move things around, we'll set up column 'mapping' in the code.
This will allow it to work right from the outset as you have things set up
now, and will permit you to rearrange things in either book later, with just
a little code alteration to keep it working with any such new arrangement.
I'm off to work on this now - turns out the other user needs almost exactly
the same thing [didn't really want automatic updates in real time, so I'm
converting that code to 'on-demand' macro and can get double-duty-use out of
it :-) ]

"Alan Auerbach" wrote:

Thank you so much for offering to assist me!

The keys for the shorter workbook are in column A and for the longer
workbook they are in column B, but there is no reason I cannot delete column
A which would put all the keys in column A. Once the original column A is
deleted, the address data in the longer workbook is in columns J, K, L, M and
N. The address data in the shorter workbook (to be replaced with information
from the longer workbook) is in columns F, G, H, I and J.

The goal is to use the keys in the shorter workbook to lookup the data in
the longer workbook and, for each key in the shorter workbook, to replace the
data in columns F, G, H, I and J with the data in the longer workbook located
in columns J, K, L, M and N.

"JLatham" wrote:

I don't have the code ready to post here right this minute. But I just
finished up helping someone who contacted me direct with something similar -
but they wanted the moves from one workbook to the other to happen
automatically, so the code is not in the form that could be tossed right into
a macro at the moment. If no one else responds very shortly, I'll modify the
code and put it up here and you can alter it to suit you needs. How many
columns of information need to be moved from each row from one book to the
other?

"Alan Auerbach" wrote:

I have two workbooks with different information--and a different number of
records--but a common column that can be used as a key. Can the smaller
workbook be used, with the common keys, to look up rows in the second
workbook and either import the data in specified columns or replace the data
in specified columns in the shorter workbook with data in the specified
columns in the larger workbook? To be explicit, the two workbooks have
address data that needs to be replaced in one.

I will greatly appreciate any comments and assistnace for creating this merge.

/s/ Alan Auerbach

The code is rather long-lined at times and the editor here tends to break
things up even when I try to outguess it. So I've uploaded two workbooks you
can download that should do the trick for you. I think I have them set up
(except for workbook and worksheet names within the code that you'll need to
provide) to work for you.
This workbook:
http://www.jlathamsite.com/uploads/A...SourceBook.xls
is the one with the actual code in it.
This workbook
http://www.jlathamsite.com/uploads/AA_LongerBook.xls
simply emulates the second workbook that you want to move data into or update.

But together they also give some insight as to how the code is set up to
work. They were both saved in Excel 97-2003 format.

If you have any questions, you can ask here or via email to (remove spaces)
HelpFrom @ jlathamsite.com
I've tried to comment the code enough, especially the column mapping portion
well enough that anyone could make changes to it. It is actually a pretty
'generic' routine for such an operation - as long as all data exists in
columns with single-letter column identifiers (A through Z).

"Alan Auerbach" wrote:

You are a saint! I do not know how to thank you. I would reciprocate, but I
know that my skills are far inferior to your own. :-)

Thank you!

"JLatham" wrote:

No need to move things around, we'll set up column 'mapping' in the code.
This will allow it to work right from the outset as you have things set up
now, and will permit you to rearrange things in either book later, with just
a little code alteration to keep it working with any such new arrangement.
I'm off to work on this now - turns out the other user needs almost exactly
the same thing [didn't really want automatic updates in real time, so I'm
converting that code to 'on-demand' macro and can get double-duty-use out of
it :-) ]

"Alan Auerbach" wrote:

Thank you so much for offering to assist me!

The keys for the shorter workbook are in column A and for the longer
workbook they are in column B, but there is no reason I cannot delete column
A which would put all the keys in column A. Once the original column A is
deleted, the address data in the longer workbook is in columns J, K, L, M and
N. The address data in the shorter workbook (to be replaced with information
from the longer workbook) is in columns F, G, H, I and J.

The goal is to use the keys in the shorter workbook to lookup the data in
the longer workbook and, for each key in the shorter workbook, to replace the
data in columns F, G, H, I and J with the data in the longer workbook located
in columns J, K, L, M and N.

"JLatham" wrote:

I don't have the code ready to post here right this minute. But I just
finished up helping someone who contacted me direct with something similar -
but they wanted the moves from one workbook to the other to happen
automatically, so the code is not in the form that could be tossed right into
a macro at the moment. If no one else responds very shortly, I'll modify the
code and put it up here and you can alter it to suit you needs. How many
columns of information need to be moved from each row from one book to the
other?

"Alan Auerbach" wrote:

I have two workbooks with different information--and a different number of
records--but a common column that can be used as a key. Can the smaller
workbook be used, with the common keys, to look up rows in the second
workbook and either import the data in specified columns or replace the data
in specified columns in the shorter workbook with data in the specified
columns in the larger workbook? To be explicit, the two workbooks have
address data that needs to be replaced in one.

I will greatly appreciate any comments and assistnace for creating this merge.

/s/ Alan Auerbach