#1
|
|||
|
|||
Comparing two excel worksheets
Hello all
I hope someone can help with my query as I'm currently struggling to overcome it. I have an excel spreadsheet with 3 worksheets. Worksheet 1 has a list of all the records on our database and Worksheet 2 has a list of all the records on our database that are out of date. I want to compare the two worksheets and then list any records that are NOT on both of the worksheets in a list on Worksheet 3. I am trying to design a macro to look at column A (which has the customer reference numbers) on both Worksheets 1 and 2 and when it finds any non-duplicates it copies the entire row and pastes it into Worksheet 3. The rows in Worksheets 1 and 2 have slight differences even if the customer reference number is the same, so I need to compare just the customer reference number rather than the whole row but then copy the entire row after it finds non-duplicates. I hope that makes sense.. I hope someone can help. Many thanks for your time. |
#2
|
||||
|
||||
Hi SaneMan,
To find out which records on Sheet2 are not found in Sheet 1, you could use a formula like: =IF(COUNTIF(Sheet1!C:C,Sheet2!C2)=0,Sheet2!C2,"") where 'C' is the data column. This will return a discontiguous list of unmatched records. To do it for both sheets, you'd also need a separate test for: =IF(COUNTIF(Sheet2!C:C,Sheet1!C2)=0,Sheet1!C2,"")
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Protecting Worksheets in Excel 2010 | b-baker | Excel | 1 | 04-27-2012 01:38 AM |
Merge from 2 excel worksheets | apolloman | Mail Merge | 2 | 06-02-2011 04:16 AM |
Comparing Data - MS Excel | ramadevidokkuud | Excel | 1 | 05-19-2011 05:52 AM |
Relinking excel worksheets | iffy | Excel | 2 | 01-07-2011 01:53 PM |
Comparing Data | leroytrolley | Excel | 1 | 08-09-2008 08:34 PM |