Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-25-2012, 08:36 AM
SaneMan SaneMan is offline Comparing two excel worksheets Windows 98/ME Comparing two excel worksheets Office 2003
Novice
Comparing two excel worksheets
 
Join Date: Jan 2011
Posts: 20
SaneMan is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 06-27-2012, 07:52 PM
macropod's Avatar
macropod macropod is offline Comparing two excel worksheets Windows 7 64bit Comparing two excel worksheets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,503
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

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
Comparing two excel worksheets 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
Comparing two excel worksheets Relinking excel worksheets iffy Excel 2 01-07-2011 01:53 PM
Comparing two excel worksheets Comparing Data leroytrolley Excel 1 08-09-2008 08:34 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft