Find common and unique values in two arrays

[student123]

I have two arrays of values. I would like to create new arrays that contain values that are common between the two arrays and values unique in each array. I can solve the problem by using "for next" loop. But I want to know if there are Excel functions that can solve the problem more efficiently.

Here is a simple example of what I want to do

Array1: 1,2,3,4,5,6,12,8,9,10
Array2: 1,2,3,11,5,6,7,8,9,10

I would like to generate a new array that has values common to both:
Array3: 1,2,3,5,6,8,9,10

Array that has unique values in Array1:

Array4: 4,12

and one that has unique values in Array2:

Array5: 11,7

I thought functions like "intersect" would help solve such a problem, but I am not sure how to use them.

Thanks for your help.

student123

 

[venkat1926]

many solution may be available

sample data is like this

<table width="704" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="11"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">hdng1</td> <td style="width: 48pt;" width="64">hdng2</td> <td style="width: 48pt;" width="64">hdng3</td> <td style="width: 48pt;" width="64">hdng4</td> <td style="width: 48pt;" width="64">hdng5</td> <td style="width: 48pt;" width="64">hdng6</td> <td style="width: 48pt;" width="64">hdng7</td> <td style="width: 48pt;" width="64">hdng8</td> <td style="width: 48pt;" width="64">hdng9</td> <td style="width: 48pt;" width="64">hdng10</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">2</td> <td align="right">3</td> <td align="right">4</td> <td align="right">5</td> <td align="right">6</td> <td align="right">12</td> <td align="right">8</td> <td align="right">9</td> <td align="right">10</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">2</td> <td align="right">3</td> <td align="right">11</td> <td align="right">5</td> <td align="right">6</td> <td align="right">7</td> <td align="right">8</td> <td align="right">9</td> <td align="right">10</td> <td>
</td> </tr> </tbody></table>now try this macro test

Sub test()
Dim r As Range, r1 As Range, c As Range, c1 As Range, j As Range, dest As Range
Dim cfind As Range
Set r = Range(Range("A2"), Range("A2").End(xlToRight))
Set r1 = Range(Range("A3"), Range("A3").End(xlToRight))


For Each c In r
Set cfind = r1.Cells.Find(what:=c.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Set dest = Range("A5")
dest = "common items"
Cells(dest.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = c
Else
Set dest = Range("a7")
dest = "items in array1 and not in array2"
Cells(dest.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = c
End If
Next c
For Each c1 In r1
Set cfind = r.Cells.Find(what:=c1.Value, lookat:=xlWhole)
If cfind Is Nothing Then
Set dest = Range("A9")
dest = "items in array2 not ina array1"
Cells(dest.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = c1
End If
Next c1


End Sub

you will get lilke this

<table width="704" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="11"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">hdng1</td> <td style="width: 48pt;" width="64">hdng2</td> <td style="width: 48pt;" width="64">hdng3</td> <td style="width: 48pt;" width="64">hdng4</td> <td style="width: 48pt;" width="64">hdng5</td> <td style="width: 48pt;" width="64">hdng6</td> <td style="width: 48pt;" width="64">hdng7</td> <td style="width: 48pt;" width="64">hdng8</td> <td style="width: 48pt;" width="64">hdng9</td> <td style="width: 48pt;" width="64">hdng10</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">2</td> <td align="right">3</td> <td align="right">4</td> <td align="right">5</td> <td align="right">6</td> <td align="right">12</td> <td align="right">8</td> <td align="right">9</td> <td align="right">10</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">2</td> <td align="right">3</td> <td align="right">11</td> <td align="right">5</td> <td align="right">6</td> <td align="right">7</td> <td align="right">8</td> <td align="right">9</td> <td align="right">10</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">common items</td> <td align="right">1</td> <td align="right">2</td> <td align="right">3</td> <td align="right">5</td> <td align="right">6</td> <td align="right">8</td> <td align="right">9</td> <td align="right">10</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">items in array1 and not in array2</td> <td align="right">4</td> <td align="right">12</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">items in array2 not ina array1</td> <td align="right">11</td> <td align="right">7</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>the macro needs some tweaking.

 

[student123]

Thanks for your response.

I know I can solve the problem with For Next Loop (as I asaid in my orginal posting); but I was looking for a solution that makes use of Excel built in functions like "Intersect" or some other that would simplify the solution and run faster. I am planning on working with large arraya and For Next loops take a long time.

Do you know of any?

Thanks Again.