non match result

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?

demechanik
Fri May 09 09:55:04 CDT 2008

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?

Belinda7237
Fri May 16 21:00:01 CDT 2008

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?

Max
Fri May 16 21:35:59 CDT 2008

Welcome, thanks for the closure.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Belinda7237" <Belinda7237@discussions.microsoft.com> wrote in message
news:47D94CEB-7135-4BC9-A065-8A3DC6E20C83@microsoft.com...
> thanks, this worked perfectly