Displaying data from an object range

[AJPic]

First let me say that I'm very novice to VBA and I only use it occasionally.

Anyway, I have 2 lists in separate columns of Excel and I'd like to take the union of those 2 lists and display the union in a 3rd column. How can I do this?

I started with the following code but am not sure where to go to next:

--------
Sub Test()

Set FirstList = Worksheets("Data").Range("A:A")
Set SecondList = Worksheets("Data").Range("B:B")
Set UnionObject = Union(FirstList, SecondList)

Worksheets("Data").Range("C:C") = UnionObject


End Sub
--------

Thanks

 

[rmikesmith]

Are you sure you need a Union? What data do you expect to see in Column C?

The line of code

Set UnionObject = Union(FirstList, SecondList)

creates a Range object having the address A:B (i.e., Columns A & B).


Regards,
Mike

 

[AJPic]

Well, to be honest, I'm not sure I need to use Union - I just thought it would return what I needed.

What I want to do is take the list from column A: (Joe, Mark, Tom, Sue) and my list from column B: (Mark, Sarah, Betty, Tom) and column C display the union: (Mark, Tom).

Perhaps I'm going about this wrong with the Union method?

 

[rmikesmith]

Actually, you're looking for the Intersection of the two sets. However, the Union & Intersect methods apply to Ranges and return a Range object. Ex. Range1 = A1:Z1 and Range2 = B1:B100. Instersect(Range1, Range2) would be the Range B1.

I don't think there is a built-in function to find the intersection of two sets of data, but a custom function could be written. I play around with this. In the meantime, perhaps someone else will weigh in.

Mike

 

[AJPic]

I'm trying to do too much over here and completely botched my last post. I actually do want the Union, not the Intersection. In column C, I'd like to see (Joe, Mark, Tom, Sue, Sarah, Betty).

Sorry for the confusion and thank you for your time.

 

[PHV]

Just a crude starting point:
Sub AandB2C()
Dim r As Long, a, b
For Each a In Range("A:A")
For Each b In Range("B:B")
If a.Value = b.Value Then
r = r + 1
Cells(r, "C") = a.Value
Exit For
End If
Next
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Give the following a try. The function takes two ranges and returns a collection containing the union of contents in those ranges. This uses a trick by John Walkenbach to supply unique lists to ListBoxes, but also works here. The Sub procedure shows how to use it.

Sub TestUnion()
Dim colUnion As Collection
Dim i As Long

   Set colUnion = DataUnion(ActiveSheet.Range("A1:A15"), ActiveSheet.Range("B1:B15"))
   With ActiveSheet
     For i = 1 To colUnion.Count
       .Cells(i, 3).Value = colUnion(i)
     Next i
   End With

End Sub


Function DataUnion(ByVal Rng1 As Range, ByVal Rng2 As Range) As Collection
Dim colData As Collection
Dim OneCell As Range
Dim i As Long

   Set colData = New Collection
   On Error Resume Next
   For Each OneCell In Rng1
     colData.Add OneCell.Text, OneCell.Text
   Next OneCell
   For Each OneCell In Rng2
     colData.Add OneCell.Text, OneCell.Text
   Next OneCell
   Set DataUnion = colData
   Set colData = Nothing
   
End Function

Admittedly, PHV's solution may be simpler, in this case.


Regards,
Mike

 

[AJPic]

Thanks for your help guys. It looks like I have a great start to my code.