Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-14-2009, 06:22 AM
psenku psenku is offline Finding the complements between the two data sets Windows XP Finding the complements between the two data sets Office 2007
Novice
Finding the complements between the two data sets
 
Join Date: Aug 2009
Posts: 1
psenku is on a distinguished road
Default Finding the complements between the two data sets


Hi,
I am working on a data handling wherein i am required to find the values that are in column A but not present in column B. For example, if the column A has 1,2,3,4,5 and B has 1,3,5, i want to get the values 2,4 which are not present in the column B. i am dealing with million rows and finding it difficult the get the complement records. I am using MS Office 2003. Any help is greatly appreciated.

Regards,
Senthil
Reply With Quote
  #2  
Old 08-16-2009, 04:03 AM
Rod Rod is offline Finding the complements between the two data sets Windows XP Finding the complements between the two data sets Office 2007
Novice
 
Join Date: Jul 2009
Posts: 5
Rod is on a distinguished road
Default Finding the complements between two sets of data.

Hi Senthil
This can be achieved by using an Array formula. There are some rules that must be followed for it to work. Firstly both columns of data and the results column must have exactly the same number of cells in them. Secondly when you have typed in the formula in the formula bar it must be entered by hitting the shift, ctrl and enter keys at the same time. I will give you an example to try out.
You have two columns of data List 1 and List 2.
List 1 is E15:E23 and List 2 is G17:G25 (note they do not have to start at the same row but MUST have the same number of rows, they can of course start at the same row if you wish). Now highlight the same number of rows in a different column. Then in the formula bar type in the formula:-
=IF(ISERROR(MATCH(E15:E23,G17:G25,0)),E15:E23,"")
As mentioned before you must hit shift, ctrl and enter keys simultaneously for the array formula to be activated. You will know if the Array is activated as the formula you entered in the formula bar will have a "squiggle" at the beginning and end of it. You must also enter it in the same manner whenever you change the formula.
Hope this helps - Rod
Reply With Quote
  #3  
Old 08-17-2009, 11:15 PM
penghuipan penghuipan is offline Finding the complements between the two data sets Windows 2K Finding the complements between the two data sets Office 2003
Novice
 
Join Date: Aug 2009
Posts: 2
penghuipan is on a distinguished road
Default

Hi Senthil
I think you can use the formual --Vlookup(),for example,columns A have 1,2,3,4,5,columns B have 1,2,3.in columns C.C1=vlookup($a1,$b$1:$b$3,1,0),then columns B' data will be match columns A, if not match,will be have a error symbol#N/A,The
error symbol means columns A' data not present in Columns B',so you can filter columns B' error symbol,columns B' error symbol means you want to get the value in columns A.
I hope it can be help you.

penghui.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding the complements between the two data sets How do I accept non-Western (non-latin) character sets 3rd floor Outlook 1 02-10-2006 03:17 PM
Finding the complements between the two data sets One bar chart for 3 sets of data rclloyd Excel 1 02-03-2006 09:51 AM

Other Forums: Access Forums

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


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