Sub RemoveDuplicates()
Dim Rng As Range, Cell As Range
Dim List As New Collection
Dim v As Variant
Dim item As Variant

' The items are in A1:A10
Set Rng = Range("A1:A10")

On Error Resume Next
For Each Cell In Rng
v = List.item(Cell.Text)
Debug.Print Cell.Address, Err.Number
If Err.Number < 0 Then
v = Array(Cell.Text, Cell.Address(0, 0))
List.Add v, CStr(Cell.Value)
Else
v(1) = v(1) & "," & Cell.Address(0, 0)
List.Remove Cell.Text
List.Add v, CStr(Cell.Value)
End If
Err.Clear
Next Cell

' Resume normal error handling
On Error GoTo 0


' Print out the list is the Immediate window
For Each item In List
v = item
Debug.Print v(0), v(1)
Next item


End Sub

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi there,

I'm trying to get my head around multi-dimensional collections.

What I'm trying to do is to run through a column of data and check if each
cell value is "correct" (through some other logic). If it is not correct
then I want to add the value (string) to a collection and its associated
cell reference.
I'm assuming thus far that I need a 2 dimensional collection?

Now, if further down the column I come across the same incorrect string, I
want to find the existing collection item and add another cell reference
to
it.

So (where "Bob" and "Mary" are NOT "correct"):

Bob
Mary
Susan
Bob

Would therefore be added to the collection as:

Item 1 ("Bob", "A1")
Item 2 ("Mary", "A2")
Item 1 ("Bob", "A1" & "A2")

Can anyone help me get this sorted out?

Thanks in advance

John

PS - The reason that I'm trying to use a collection rather than an array
is
that I'm assuming that it's easier to reference the elements by name
(string)? I happy to be corrected!