Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 Worksheets and return differences in a third.
I have 3 worsheets on a file.
Number one contains a list of current customers by: Column 1: Account number, Column 2: Company Name, Column 3: Adress Line 1, Column 4: City, Column 5: Zip/Postal Code and Column 6: Spend to date. Number 2 is an import table that I paste to each month with the following data supplied by my head office: Column 1: Account Number, Column 2: Company, Column 3: Spend to date. The idea was to be able to then use a VLOOKUP formula to go back to Worksheet one and sort customer spend by zip/postcode area. The VLOOKUP formula I use for this is: =IF(ISNA(VLOOKUP(A2,Special,5,FALSE)),"No Spend",(VLOOKUP(A2,Special,5,FALSE))). NOW: I want a third worksheet to tell me if there are any NEW customers on the list being sent by my head office that I didn't originally have listed on worksheet 1. This way, I can add them manually to worksheet 1 ready for next month and then the third worksheet will do the same again! Is this possible with a formula and can anyone tell me what it is and how to do it?!! Thank you in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 Worksheets and return differences in a third.
What I normally do is also do another vlookup on the list from your company.
Anything that came up with N/A are the ones that are not on Wksht 1 list. Might be too manual but it's a workaround. I'm anxious to see if there is a faster way of doing it too. "TheBigStig" wrote: I have 3 worsheets on a file. Number one contains a list of current customers by: Column 1: Account number, Column 2: Company Name, Column 3: Adress Line 1, Column 4: City, Column 5: Zip/Postal Code and Column 6: Spend to date. Number 2 is an import table that I paste to each month with the following data supplied by my head office: Column 1: Account Number, Column 2: Company, Column 3: Spend to date. The idea was to be able to then use a VLOOKUP formula to go back to Worksheet one and sort customer spend by zip/postcode area. The VLOOKUP formula I use for this is: =IF(ISNA(VLOOKUP(A2,Special,5,FALSE)),"No Spend",(VLOOKUP(A2,Special,5,FALSE))). NOW: I want a third worksheet to tell me if there are any NEW customers on the list being sent by my head office that I didn't originally have listed on worksheet 1. This way, I can add them manually to worksheet 1 ready for next month and then the third worksheet will do the same again! Is this possible with a formula and can anyone tell me what it is and how to do it?!! Thank you in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 Worksheets and return differences in a third.
Perhaps this would work as an alternative:
http://www.exceltip.com/st/Compare_t...ce l/477.html -- RyGuy "Storm" wrote: What I normally do is also do another vlookup on the list from your company. Anything that came up with N/A are the ones that are not on Wksht 1 list. Might be too manual but it's a workaround. I'm anxious to see if there is a faster way of doing it too. "TheBigStig" wrote: I have 3 worsheets on a file. Number one contains a list of current customers by: Column 1: Account number, Column 2: Company Name, Column 3: Adress Line 1, Column 4: City, Column 5: Zip/Postal Code and Column 6: Spend to date. Number 2 is an import table that I paste to each month with the following data supplied by my head office: Column 1: Account Number, Column 2: Company, Column 3: Spend to date. The idea was to be able to then use a VLOOKUP formula to go back to Worksheet one and sort customer spend by zip/postcode area. The VLOOKUP formula I use for this is: =IF(ISNA(VLOOKUP(A2,Special,5,FALSE)),"No Spend",(VLOOKUP(A2,Special,5,FALSE))). NOW: I want a third worksheet to tell me if there are any NEW customers on the list being sent by my head office that I didn't originally have listed on worksheet 1. This way, I can add them manually to worksheet 1 ready for next month and then the third worksheet will do the same again! Is this possible with a formula and can anyone tell me what it is and how to do it?!! Thank you in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 Worksheets and return differences in a third.
After looking briefly at the macro you cite, I don't think it will work. I
think that's intended to identify changes in the data on two different sheets that have the same layouts (same number of rows and columns, in the same order, etc.) The OP's first sheet has 6 columns of data, his 2nd only 3 (the address lines aren't there on the 2nd sheet), and data in column 3 on the 2nd sheet corresponds to column 6 on the first. Perhaps this would work as an alternative: http://www.exceltip.com/st/Compare_t...ce l/477.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 Worksheets and return differences in a third.
Thanks! Ill try your suggestions!
"TheBigStig" wrote: I have 3 worsheets on a file. Number one contains a list of current customers by: Column 1: Account number, Column 2: Company Name, Column 3: Adress Line 1, Column 4: City, Column 5: Zip/Postal Code and Column 6: Spend to date. Number 2 is an import table that I paste to each month with the following data supplied by my head office: Column 1: Account Number, Column 2: Company, Column 3: Spend to date. The idea was to be able to then use a VLOOKUP formula to go back to Worksheet one and sort customer spend by zip/postcode area. The VLOOKUP formula I use for this is: =IF(ISNA(VLOOKUP(A2,Special,5,FALSE)),"No Spend",(VLOOKUP(A2,Special,5,FALSE))). NOW: I want a third worksheet to tell me if there are any NEW customers on the list being sent by my head office that I didn't originally have listed on worksheet 1. This way, I can add them manually to worksheet 1 ready for next month and then the third worksheet will do the same again! Is this possible with a formula and can anyone tell me what it is and how to do it?!! Thank you in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare two columns contents for differences | Excel Worksheet Functions | |||
how do i compare two excel spreadsheets to indicate differences | Excel Worksheet Functions | |||
How do I compare two Excel worksheets for cell differences? | Excel Discussion (Misc queries) | |||
Compare 2 columns to see differences | Excel Worksheet Functions | |||
Compare two spreadsheets to find differences. | Excel Discussion (Misc queries) |