non match result

[Belinda7237]

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?

 

[Max]

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

 

[Belinda7237]

thanks, this worked perfectly

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]

Welcome, thanks for the closure.