find duplicate data in diffrent column

[Malik Nadeem]

i wanna know how to find those cell in a worksheet which are same for example

question
columnA columnB
1 9
3 2
2 8
4 7
5 6
6 5
7 4

need answer in following condition

AA)
following are maching with others
columA columnB
2 2
4 4
5 5
6 6
7 7

BB)
and these are no mach or diffrent with others
1 8
3 9

hope every think is clear if you need further please feel free to ask
basicaly it is my big problem i m losting my lot of time bcz of this manual
serching... >Malik Nadeem

 

[Max]

Here's one interp and a play to try ..

Sample construct available at:
http://www.savefile.com/files/8194366
Compare 2 numbers col n Extr match n unmatch in ascend ord.xls

Assuming source data are numbers in cols A and B,
from row1 down to a max expected row10 (say)

Place
In C1:
=IF(ROW(A1)>COUNT(H:H),"",INDEX(A:A,MATCH(SMALL(H:H,ROW(A1)),H:H,0)))
In D1:
=IF(ROW(A1)>COUNT(J:J),"",INDEX(B:B,MATCH(SMALL(J:J,ROW(A1)),J:J,0)))
In E1:
=IF(ROW(A1)>COUNT(G:G),"",INDEX(A:A,MATCH(SMALL(G:G,ROW(A1)),G:G,0)))
In F1:
=IF(ROW(A1)>COUNT(I:I),"",INDEX(B:B,MATCH(SMALL(I:I,ROW(A1)),I:I,0)))

Then place in:
In G1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",A1+ROW()/10^10))
In H1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),A1+ROW()/10^10,""))
In I1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",B1+ROW()/10^10))
In J1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),B1+ROW()/10^10,""))

Select C1:J1, fill down to J10
(cover the max expected extent of data in cols A and B)

Col C returns an ascending sort of the numbers in col A found in col B
Col D returns an ascending sort of the numbers in col B found in col A

Col E returns an ascending sort of the numbers in col A not found in col B
Col F returns an ascending sort of the numbers in col B not found in col A

Cols G to J are corresponding criteria cols
with arb tie-breakers for extracting in ascending sort order