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? |
non match result
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? |
non match result
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? |
non match result
Welcome, thanks for the closure.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Belinda7237" wrote in message ... thanks, this worked perfectly |
All times are GMT +1. The time now is 02:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com