Finding common and uncommon data from two data base

[Hrishi]

Hello, I have 2 columns with around 1000 rows in each column filled with data numbers viz. B122512, B655865 etc

Data 1	Data 2
B155425	B859575
B859575	B155425
B655864	B155425
and so on	and so on

<tbody>
</tbody>

I want to find all common numbers exciting in both Data, and all uncommon numbers as well. How to find that? plz help.

Regards

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​
1​	Data 1	Data 2		common	uncommon
2​	B155425	B859575		B155425	x
3​	B859575	B155425		B859575	B655864
4​	x	y			y
5​	B655864	B155425
6​

A2:A5 is named Data1 in the Name Manager.
B2:B5 is named Data2 in the Name Manager.

In D2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Data1,SMALL(IF(ISNUMBER(MATCH(Data1,Data2,0)),ROW(Data1)-ROW(INDEX(Data1,1,1))+1),ROWS($D$2:D2))),"")

Add the following code in VBA to your workbook as a module using ALT+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Once installed the above code, define DATA in the Name Manager as referring to:

=arrayunion(Data1,Data2)

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(DATA,SMALL(IF(1-ISNUMBER(MATCH(DATA,$D$2:$D$3,0)),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(DATA))))),ROWS($E$2:E2))),"")