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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Data 1
[/TD]
[TD]Data 2
[/TD]
[/TR]
[TR]
[TD]B155425
[/TD]
[TD]B859575
[/TD]
[/TR]
[TR]
[TD]B859575
[/TD]
[TD]B155425
[/TD]
[/TR]
[TR]
[TD]B655864
[/TD]
[TD]B155425
[/TD]
[/TR]
[TR]
[TD]and so on
[/TD]
[TD]and so on
[/TD]
[/TR]
</tbody>[/TABLE]


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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]Data 1[/td][td]Data 2[/td][td][/td][td]common[/td][td]uncommon[/td][/tr]
[tr][td]

2​

[/td][td]B155425[/td][td]B859575[/td][td][/td][td]B155425[/td][td]x[/td][/tr]
[tr][td]

3​

[/td][td]B859575[/td][td]B155425[/td][td][/td][td]B859575[/td][td]B655864[/td][/tr]
[tr][td]

4​

[/td][td]x[/td][td]y[/td][td][/td][td][/td][td]y[/td][/tr]
[tr][td]

5​

[/td][td]B655864[/td][td]B155425[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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))),"")