Merging Data from Two Different Workbooks on a Common Key

[Alan Auerbach]

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

 

[JLatham]

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]

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]

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]

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!

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 ]

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.

 

[Alan Auerbach]

Thank you once again. I will work on it this evening.

I would like to know a little bit about you since you have been so helpful.
What is your name? What do you do? We are in the business of manufacturng
sanitizing equipment for swimming pools (http://www.poolting.com).

Best wishes,
/s/ Alan
(e-mail address removed)

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/AA_ShortSourceBook.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).

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!

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 ]

:

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.

:

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?

:

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

 

[JLatham]

First name is Jerry. If you're viewing this in the Microsoft Excel
discussion forums, you get a clue right away that I am one of Microsoft's
MVPs - in the Excel group. I was selected MVP in January 2006 for work done
in Windows Server Networking, but that really is not my strong suit (you
would not want me as your SysAdmin!) and at my request, I was evaluated for
induction into the Excel group and it seems that I managed to slip through
some crack and be awarded MVP in the Excel group.

I've been programming since about 1979 on everything from desktops to
mini-computers such as the Data General Nova 3/12 and MV7000 series. I was
trained in programming by the U.S. Air Force and was in one of the last
classes to have to submit our Honeywell 6000 assembly language programs on
Holerith cards (you may know them as IBM punch-cards). After completing that
school, I was part of a group of 24 air traffic controllers (ATC), ATC radar
maintenance personnel and GS-types that had been sent through the same school
and maintained the USAF's primary fixed air traffic control radar systems
software (I was one of the ATC types that received the additional training).
I have a MOS certificate from Microsoft for Office 2003, and I'm struggling
to catch up with the changes made in Office 2007.

Currently I work for a company that provides training, engineering and IT/IS
services (including real SysAdmin types) to several government agencies. I'm
part of their headquarters staff and wear several hats including being the
IT/IS/Help Desk guy around the office of 10 people (myself included). With
over 240 employees working at dispersed locations we have to get a lot done
by our headquarters group and I often provide automated solutions in Excel
and/or Access to get more work done in less time than previously used.

Thank you once again. I will work on it this evening.

I would like to know a little bit about you since you have been so helpful.
What is your name? What do you do? We are in the business of manufacturng
sanitizing equipment for swimming pools (http://www.poolting.com).

Best wishes,
/s/ Alan
(e-mail address removed)

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/AA_ShortSourceBook.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).

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!

:

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 ]

:

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.

:

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?

:

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

 

[Luther]

Hello,

I am also interested in using the routine, but it seems that the pages have
been changed/removed from your website. May I be directed to another link to
download them, please? I have been searching for this a long time !!

Luther

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/AA_ShortSourceBook.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).

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!

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 ]

:

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.

:

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?

:

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

 

[JLatham]

I've put the workbooks back out on the site. The links are functional again -
Even though I've got a pretty good chunk of real estate at the site, I do
clean it out from time to time.

Hello,

I am also interested in using the routine, but it seems that the pages have
been changed/removed from your website. May I be directed to another link to
download them, please? I have been searching for this a long time !!

Luther

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/AA_ShortSourceBook.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).

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!

:

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 ]

:

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.

:

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?

:

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

 

[Julie]

Hi Jerry,
Your code provided here has been a godsend. I know this post is old but
could you help me with a variation to this. The code puts the data from the
source sheet to the next blank row in the destination sheet. I would like it
to put the information from the source sheet into the same row on the
destination sheet where the key data matches.

Many Thanks in advance...

I've put the workbooks back out on the site. The links are functional again -
Even though I've got a pretty good chunk of real estate at the site, I do
clean it out from time to time.

Hello,

I am also interested in using the routine, but it seems that the pages have
been changed/removed from your website. May I be directed to another link to
download them, please? I have been searching for this a long time !!

Luther

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/AA_ShortSourceBook.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).

:

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!

:

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 ]

:

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.

:

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?

:

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