Finding the complements between the two data sets

[psenku]

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

[Rod]

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

[penghuipan]

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.