Combining Data
 

I have a question I am hoping someone can help me with. I have 2 worksheets
that I need to merge the data. Both have a common field that I key on, Case
ID. If the Case ID is found in both worksheets, the data will be pulled from
worksheet 2. If the Case ID is exists in worksheet 1 but not 2, the data is
pulled from worksheet 1. If the Case ID exists in worksheet 2 but not 1, the
data is pulled from worksheet 2.
Basically, I am exporting data from a system that only gives me current
data. That file may contain existing cases or new cases that the old file
does not. The old file may contain older cases that the new file does not.
I need to retain the old, update the existing and add the new.
Thanks for the help!

Sounds as though you should use worksheet 2 as your 'master,' and copy into
it all the rows in worksheet 1 that are missing from 2. To determine which
ones to copy, insert a new helper col in worksheet1 next to the CaseID
column. In the new column, use a COUNTIF() formula to determine if the
CaseID is in the Master sheet. Assuming the helper column is col A, the case
IDs are in col B on wrksheet 1 and col A on wrksheet 2, use this formula in
cell A2:

=COUNTIF(Worksheet2_Name!A:A,B2)

Copy that formula down to the end of your data, then sort on col A.
Anything with a formula result of 0 is missing from worksheet 2. Copy all
those rows and paste them into worksheet 2



"Lori" wrote:

I have a question I am hoping someone can help me with. I have 2 worksheets
that I need to merge the data. Both have a common field that I key on, Case
ID. If the Case ID is found in both worksheets, the data will be pulled from
worksheet 2. If the Case ID is exists in worksheet 1 but not 2, the data is
pulled from worksheet 1. If the Case ID exists in worksheet 2 but not 1, the
data is pulled from worksheet 2.
Basically, I am exporting data from a system that only gives me current
data. That file may contain existing cases or new cases that the old file
does not. The old file may contain older cases that the new file does not.
I need to retain the old, update the existing and add the new.
Thanks for the help!

This would work except that I need this to be automated. The data will be
exported daily. Therefore the new worksheet would become the old each day.
In addition, there are numerous pivot tables reporting off of the worksheet.
We would like to be able to just push a button and the data merge together
rather than have a manual process.

"Duke Carey" wrote:

Sounds as though you should use worksheet 2 as your 'master,' and copy into
it all the rows in worksheet 1 that are missing from 2. To determine which
ones to copy, insert a new helper col in worksheet1 next to the CaseID
column. In the new column, use a COUNTIF() formula to determine if the
CaseID is in the Master sheet. Assuming the helper column is col A, the case
IDs are in col B on wrksheet 1 and col A on wrksheet 2, use this formula in
cell A2:

=COUNTIF(Worksheet2_Name!A:A,B2)

Copy that formula down to the end of your data, then sort on col A.
Anything with a formula result of 0 is missing from worksheet 2. Copy all
those rows and paste them into worksheet 2



"Lori" wrote:

I have a question I am hoping someone can help me with. I have 2 worksheets
that I need to merge the data. Both have a common field that I key on, Case
ID. If the Case ID is found in both worksheets, the data will be pulled from
worksheet 2. If the Case ID is exists in worksheet 1 but not 2, the data is
pulled from worksheet 1. If the Case ID exists in worksheet 2 but not 1, the
data is pulled from worksheet 2.
Basically, I am exporting data from a system that only gives me current
data. That file may contain existing cases or new cases that the old file
does not. The old file may contain older cases that the new file does not.
I need to retain the old, update the existing and add the new.
Thanks for the help!

What you're looking for is a carefully written VBA routine that automates
pretty much the same steps that I described, but includes error checking and
validation. The approach taken may be dictated by the layout of your
worksheets and the volume of data.

Perhaps starting a new post with some samples of the data would allow
somebody here to give concise pointers on how to apporach this.

Good luck


"Lori" wrote:

This would work except that I need this to be automated. The data will be
exported daily. Therefore the new worksheet would become the old each day.
In addition, there are numerous pivot tables reporting off of the worksheet.
We would like to be able to just push a button and the data merge together
rather than have a manual process.

"Duke Carey" wrote:

Sounds as though you should use worksheet 2 as your 'master,' and copy into
it all the rows in worksheet 1 that are missing from 2. To determine which
ones to copy, insert a new helper col in worksheet1 next to the CaseID
column. In the new column, use a COUNTIF() formula to determine if the
CaseID is in the Master sheet. Assuming the helper column is col A, the case
IDs are in col B on wrksheet 1 and col A on wrksheet 2, use this formula in
cell A2:

=COUNTIF(Worksheet2_Name!A:A,B2)

Copy that formula down to the end of your data, then sort on col A.
Anything with a formula result of 0 is missing from worksheet 2. Copy all
those rows and paste them into worksheet 2



"Lori" wrote:

I have a question I am hoping someone can help me with. I have 2 worksheets
that I need to merge the data. Both have a common field that I key on, Case
ID. If the Case ID is found in both worksheets, the data will be pulled from
worksheet 2. If the Case ID is exists in worksheet 1 but not 2, the data is
pulled from worksheet 1. If the Case ID exists in worksheet 2 but not 1, the
data is pulled from worksheet 2.
Basically, I am exporting data from a system that only gives me current
data. That file may contain existing cases or new cases that the old file
does not. The old file may contain older cases that the new file does not.
I need to retain the old, update the existing and add the new.
Thanks for the help!