I have two worksheets. Worksheet 1 represents a bulk listing of all unpaid
bills by customer number and its run at the beginning of the month.
Worksheet 2 represents the bulk listing of unpaid bills at midmonth. I want
to be able to compare the worksheets by customer number and do the following:

If a customer number is found on both worksheets then they have not paid
their bill thus i will do nothing.

If a customer number is not found on worksheet two then it has been paid
therefore on worksheet 1 i want to insert a comment "cleared" in a status
field on worksheet 1.

If a customer number is on worksheet 2 but is not on the master worksheet 1
then i want it added to worksheet 1.

Worksheet 1 is a master running list.

What is the best way to accomplish this?

Some thoughts on your 3Qs

Assume cust# in both Sheet1 & 2 runs in A2 down
Q1 & Q2
In Sheet1, put in B2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0))," cleared","do nothing"))
Copy down to last row of data in col A. Adapt the returns to taste.

Q3
In Sheet2, put in B2, copy down:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0))," ","x"))
Copy down to last row of data in col A, which flags results as "x"
Apply autofilter on col B for "x", then copy n paste as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Belinda7237" wrote:
I have two worksheets. Worksheet 1 represents a bulk listing of all unpaid
bills by customer number and its run at the beginning of the month.
Worksheet 2 represents the bulk listing of unpaid bills at midmonth. I want
to be able to compare the worksheets by customer number and do the following:

Q1
If a customer number is found on both worksheets then they have not paid
their bill thus i will do nothing.

Q2
If a customer number is not found on worksheet two then it has been paid
therefore on worksheet 1 i want to insert a comment "cleared" in a status
field on worksheet 1.

Q3
If a customer number is on worksheet 2 but is not on the master worksheet 1
then i want it added to worksheet 1.

Worksheet 1 is a master running list.

What is the best way to accomplish this?

thanks, this worked perfectly

"Max" wrote:

Some thoughts on your 3Qs

Assume cust# in both Sheet1 & 2 runs in A2 down
Q1 & Q2
In Sheet1, put in B2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0))," cleared","do nothing"))
Copy down to last row of data in col A. Adapt the returns to taste.

Q3
In Sheet2, put in B2, copy down:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0))," ","x"))
Copy down to last row of data in col A, which flags results as "x"
Apply autofilter on col B for "x", then copy n paste as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Belinda7237" wrote:
I have two worksheets. Worksheet 1 represents a bulk listing of all unpaid
bills by customer number and its run at the beginning of the month.
Worksheet 2 represents the bulk listing of unpaid bills at midmonth. I want
to be able to compare the worksheets by customer number and do the following:

Q1
If a customer number is found on both worksheets then they have not paid
their bill thus i will do nothing.

Q2
If a customer number is not found on worksheet two then it has been paid
therefore on worksheet 1 i want to insert a comment "cleared" in a status
field on worksheet 1.

Q3
If a customer number is on worksheet 2 but is not on the master worksheet 1
then i want it added to worksheet 1.

Worksheet 1 is a master running list.

What is the best way to accomplish this?

Welcome, thanks for the closure.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Belinda7237" wrote in message
...
thanks, this worked perfectly