removing duplicates in lists
 

Hi,

If say I have 3 lists of data (1 in column A, 1 in B and 1 in C), is there a
way that I can get Column D to produce a list of all the unique values from
A,B,C.

IE:

Col A Col B Col C....... Therefore Col D =
a t z a
b q w b
c a a c
t
q
z
w

TIA.

removing duplicates in lists
 

I very good solution for this. Good reading

http://j-walk.com/ss/excel/tips/tip15.htm

somethinglikeant

removing duplicates in lists
 

Sub GetUniqueList()
Dim noDupes As New Collection
Dim v(1 To 3) As Variant
Dim v1 As Variant, itm as Variant
Dim i As Long, j As Long
With Worksheets("Sheet1")
v(1) = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Value
v(2) = .Range(.Cells(1, 2), .Cells(1, 2).End(xlDown)).Value
v(3) = .Range(.Cells(1, 3), .Cells(1, 3).End(xlDown)).Value
End With
On Error Resume Next
For i = 1 To 3
For j = LBound(v(i)) To UBound(v(i))
noDupes.Add v(i)(j, 1), CStr(v(i)(j, 1))
Next
Next
Debug.Print noDupes.Count
On Error GoTo 0
ReDim v1(1 To noDupes.Count, 1 To 1)
i = 0
For Each itm In noDupes
i = i + 1
v1(i, 1) = itm
Next
Range("D1").Resize(noDupes.Count, 1).Value = v1

End Sub


--
Regards,
Tom Ogilvy



"red6000" wrote in message
...
Hi,

If say I have 3 lists of data (1 in column A, 1 in B and 1 in C), is there
a
way that I can get Column D to produce a list of all the unique values
from
A,B,C.

IE:

Col A Col B Col C....... Therefore Col D =
a t z a
b q w b
c a a c
t
q
z
w

TIA.