"Merge Worksheets"
 

I have 2 worksheets (in the same workbook). Both worksheets contain much of
the same information, however, some columns have been updated (changed from
unpaid to paid, add a check number, deposit date...) I would like to
consolidate this information without duplicating data. Is this possible?
See Example:

Sheet 1
Name Address Paid Check # Deposit Date
Bob 123 1st o

Sheet 2
Name Address Paid Check # Deposit Date
Bob 123 1st x 456 6/6/08

"Merge Worksheets"
 

I need more information.

1) Do the workbook have the same data in the same rows?

2) Are there more rows in one sheet than the other?

3) What makes the rows unique (is comparing the name and address will be
unique or can the same address and name have more than one row)?

You can either do this with a worksheet function or a macro depending on
your answers above.
"carlson120800" wrote:

I have 2 worksheets (in the same workbook). Both worksheets contain much of
the same information, however, some columns have been updated (changed from
unpaid to paid, add a check number, deposit date...) I would like to
consolidate this information without duplicating data. Is this possible?
See Example:

Sheet 1
Name Address Paid Check # Deposit Date
Bob 123 1st o

Sheet 2
Name Address Paid Check # Deposit Date
Bob 123 1st x 456 6/6/08

"Merge Worksheets"
 

Thank you so much for your prompt response.
1. The worksheets do have the same information in the same rows.
2. Yes there are more rows in one sheet than the other.
3. It is rare, however, it does happen that the same row can contain the
same Name and Address.

What happened is I have a location in that sends me this data. I am trying
to audit the data, however they are updating each day with new customers
(additional rows) while I am updating the old data - paid, deposit date, ect.
I am trying to get us up on Microsoft office live, however, they are not the
most computer savy and it has not been successful. I don't know a thing
about Macros or even where to begin??

Your help is appreciated

"Joel" wrote:

I need more information.

1) Do the workbook have the same data in the same rows?

2) Are there more rows in one sheet than the other?

3) What makes the rows unique (is comparing the name and address will be
unique or can the same address and name have more than one row)?

You can either do this with a worksheet function or a macro depending on
your answers above.
"carlson120800" wrote:

I have 2 worksheets (in the same workbook). Both worksheets contain much of
the same information, however, some columns have been updated (changed from
unpaid to paid, add a check number, deposit date...) I would like to
consolidate this information without duplicating data. Is this possible?
See Example:

Sheet 1
Name Address Paid Check # Deposit Date
Bob 123 1st o

Sheet 2
Name Address Paid Check # Deposit Date
Bob 123 1st x 456 6/6/08

"Merge Worksheets"
 

first it doesn't matter if a name appears more than once because the two
worksheeetsw have the same data in the same rows.

I can do this with either a macro or worksheet functions. I'm going to use
a worksheet function.


In row one of sheet3 add these five formulas and then copy down all the rows

Cell A1
=sheet1!A1

Cell B1
=sheet1!B1

Cell C1
=sheet1!C1

cell D1
=if(sheet1!D1="",sheet2!D1,sheet1!D1)

cell E1
=if(sheet1!E1="",sheet2!E1,sheet1!E1)

"carlson120800" wrote:

Thank you so much for your prompt response.
1. The worksheets do have the same information in the same rows.
2. Yes there are more rows in one sheet than the other.
3. It is rare, however, it does happen that the same row can contain the
same Name and Address.

What happened is I have a location in that sends me this data. I am trying
to audit the data, however they are updating each day with new customers
(additional rows) while I am updating the old data - paid, deposit date, ect.
I am trying to get us up on Microsoft office live, however, they are not the
most computer savy and it has not been successful. I don't know a thing
about Macros or even where to begin??

Your help is appreciated

"Joel" wrote:

I need more information.

1) Do the workbook have the same data in the same rows?

2) Are there more rows in one sheet than the other?

3) What makes the rows unique (is comparing the name and address will be
unique or can the same address and name have more than one row)?

You can either do this with a worksheet function or a macro depending on
your answers above.
"carlson120800" wrote:

I have 2 worksheets (in the same workbook). Both worksheets contain much of
the same information, however, some columns have been updated (changed from
unpaid to paid, add a check number, deposit date...) I would like to
consolidate this information without duplicating data. Is this possible?
See Example:

Sheet 1
Name Address Paid Check # Deposit Date
Bob 123 1st o

Sheet 2
Name Address Paid Check # Deposit Date
Bob 123 1st x 456 6/6/08

"Merge Worksheets"
 

With this, I would understand that the data could not have been resorted for
this to work?

"Joel" wrote:

first it doesn't matter if a name appears more than once because the two
worksheeetsw have the same data in the same rows.

I can do this with either a macro or worksheet functions. I'm going to use
a worksheet function.


In row one of sheet3 add these five formulas and then copy down all the rows

Cell A1
=sheet1!A1

Cell B1
=sheet1!B1

Cell C1
=sheet1!C1

cell D1
=if(sheet1!D1="",sheet2!D1,sheet1!D1)

cell E1
=if(sheet1!E1="",sheet2!E1,sheet1!E1)

"carlson120800" wrote:

Thank you so much for your prompt response.
1. The worksheets do have the same information in the same rows.
2. Yes there are more rows in one sheet than the other.
3. It is rare, however, it does happen that the same row can contain the
same Name and Address.

What happened is I have a location in that sends me this data. I am trying
to audit the data, however they are updating each day with new customers
(additional rows) while I am updating the old data - paid, deposit date, ect.
I am trying to get us up on Microsoft office live, however, they are not the
most computer savy and it has not been successful. I don't know a thing
about Macros or even where to begin??

Your help is appreciated

"Joel" wrote:

I need more information.

1) Do the workbook have the same data in the same rows?

2) Are there more rows in one sheet than the other?

3) What makes the rows unique (is comparing the name and address will be
unique or can the same address and name have more than one row)?

You can either do this with a worksheet function or a macro depending on
your answers above.
"carlson120800" wrote:

I have 2 worksheets (in the same workbook). Both worksheets contain much of
the same information, however, some columns have been updated (changed from
unpaid to paid, add a check number, deposit date...) I would like to
consolidate this information without duplicating data. Is this possible?
See Example:

Sheet 1
Name Address Paid Check # Deposit Date
Bob 123 1st o

Sheet 2
Name Address Paid Check # Deposit Date
Bob 123 1st x 456 6/6/08

"Merge Worksheets"
 

The problem with resorting is the duplicate rows with the same name and
address. Otherwise, a lookup function can be used.

I got the c column formula incorrect. the best way to do the check paid is
to look at the check number column and check if it has data

C1
=if(D1="","o","x"


"carlson120800" wrote:

With this, I would understand that the data could not have been resorted for
this to work?

"Joel" wrote:

first it doesn't matter if a name appears more than once because the two
worksheeetsw have the same data in the same rows.

I can do this with either a macro or worksheet functions. I'm going to use
a worksheet function.


In row one of sheet3 add these five formulas and then copy down all the rows

Cell A1
=sheet1!A1

Cell B1
=sheet1!B1

Cell C1
=sheet1!C1

cell D1
=if(sheet1!D1="",sheet2!D1,sheet1!D1)

cell E1
=if(sheet1!E1="",sheet2!E1,sheet1!E1)

"carlson120800" wrote:

Thank you so much for your prompt response.
1. The worksheets do have the same information in the same rows.
2. Yes there are more rows in one sheet than the other.
3. It is rare, however, it does happen that the same row can contain the
same Name and Address.

What happened is I have a location in that sends me this data. I am trying
to audit the data, however they are updating each day with new customers
(additional rows) while I am updating the old data - paid, deposit date, ect.
I am trying to get us up on Microsoft office live, however, they are not the
most computer savy and it has not been successful. I don't know a thing
about Macros or even where to begin??

Your help is appreciated

"Joel" wrote:

I need more information.

1) Do the workbook have the same data in the same rows?

2) Are there more rows in one sheet than the other?

3) What makes the rows unique (is comparing the name and address will be
unique or can the same address and name have more than one row)?

You can either do this with a worksheet function or a macro depending on
your answers above.
"carlson120800" wrote:

I have 2 worksheets (in the same workbook). Both worksheets contain much of
the same information, however, some columns have been updated (changed from
unpaid to paid, add a check number, deposit date...) I would like to
consolidate this information without duplicating data. Is this possible?
See Example:

Sheet 1
Name Address Paid Check # Deposit Date
Bob 123 1st o

Sheet 2
Name Address Paid Check # Deposit Date
Bob 123 1st x 456 6/6/08

"Merge Worksheets"
 

Joel,

I am not sure what those formulas did - however, you are wonderful!!! I have
been trying to do this for days!! Thank you so much. I also figured out that
I can run a pivot table off this new data - what a great day!!

Thank you

"Joel" wrote:

first it doesn't matter if a name appears more than once because the two
worksheeetsw have the same data in the same rows.

I can do this with either a macro or worksheet functions. I'm going to use
a worksheet function.


In row one of sheet3 add these five formulas and then copy down all the rows

Cell A1
=sheet1!A1

Cell B1
=sheet1!B1

Cell C1
=sheet1!C1

cell D1
=if(sheet1!D1="",sheet2!D1,sheet1!D1)

cell E1
=if(sheet1!E1="",sheet2!E1,sheet1!E1)

"carlson120800" wrote:

Thank you so much for your prompt response.
1. The worksheets do have the same information in the same rows.
2. Yes there are more rows in one sheet than the other.
3. It is rare, however, it does happen that the same row can contain the
same Name and Address.

What happened is I have a location in that sends me this data. I am trying
to audit the data, however they are updating each day with new customers
(additional rows) while I am updating the old data - paid, deposit date, ect.
I am trying to get us up on Microsoft office live, however, they are not the
most computer savy and it has not been successful. I don't know a thing
about Macros or even where to begin??

Your help is appreciated

"Joel" wrote:

I need more information.

1) Do the workbook have the same data in the same rows?

2) Are there more rows in one sheet than the other?

3) What makes the rows unique (is comparing the name and address will be
unique or can the same address and name have more than one row)?

You can either do this with a worksheet function or a macro depending on
your answers above.
"carlson120800" wrote:

I have 2 worksheets (in the same workbook). Both worksheets contain much of
the same information, however, some columns have been updated (changed from
unpaid to paid, add a check number, deposit date...) I would like to
consolidate this information without duplicating data. Is this possible?
See Example:

Sheet 1
Name Address Paid Check # Deposit Date
Bob 123 1st o

Sheet 2
Name Address Paid Check # Deposit Date
Bob 123 1st x 456 6/6/08