VBA - Remove duplicate values in lookup

[slyhook]

I am using a VBA function to take a cell, look it up on another sheet and return (and combine) the data from several columns on that other sheet. Works, except that it returns duplicate values. Any ideas on fixing that much appreciated.

Example of what I currently get. Used "•" to denote TAB -- Sorry, wasn't sure how else to show it

Key • Result1 • Result2 • Result3 • Etc…
A1 • ABC; ABC; AB3 • RF45; LF67; LF67 • opn; opn; opn
A1 • ABC; ABC; AB3 • RF45; LF67; LF67 • opn; opn; opn
A1 • ABC; ABC; AB3 • RF45; LF67; LF67 • opn; opn; opn
BB2 • BCD; BCD • GP909; N54 • wet2; cls
CCC3 • CDE; CDE; CDE • PR1; PR1; PR2 • ptl; ptl; ptl
CCC3 • CDE; CDE; CDE • PR1; PR1; PR2 • ptl; ptl; ptl
CCC3 • CDE; CDE; CDE • PR1; PR1; PR2 • ptl; ptl; ptl
A1 • ABC; ABC; AB3 • RF45; LF67; LF67 • opn; opn; opn
CCC3 • CDE; CDE; CDE • PR1; PR1; PR2 • ptl; ptl; ptl
Etc…

What I want-- no duplicated data

A1 • ABC; AB3 • RF45; LF67 • opn
A1 • ABC; AB3 • RF45; LF67 • opn
A1 • ABC; AB3 • RF45; LF67 • opn
BB2 • BCD • GP909; N54 • wet2; cls
CCC3 • CDE • PR1; PR2 • ptl
CCC3 • CDE • PR1; PR2 • ptl
CCC3 • CDE • PR1; PR2 • ptl
A1 • ABC; AB3 • RF45; LF67 • opn
CCC3 • CDE • PR1; PR2 • ptl

Here is the code I am using -- I have tried a number of things, but none working for me.

Public Function MPartsLOOKUP(lookup_value As Variant, table_array As Range, _
col_index_num As Long) As Variant

Application.Volatile (False)
Dim Cell As Range
Dim A, B
A = ""
MPartsLOOKUP = CVErr(xlErrNA)

Set table_array = Intersect(table_array, table_array.Parent.UsedRange)
If table_array Is Nothing Then Exit Function

For Each Cell In Union(table_array.Columns(1), table_array.Cells(1))
If Cell = lookup_value Then
If A <> "" Then
A = A & "; " & Cell.Offset(0, col_index_num - 1)
GoTo 10
End If


A = Cell.Offset(0, col_index_num - 1)
End If

10 Next Cell
If A = "" Then MPartsLOOKUP = ""
MPartsLOOKUP = A

End Function

Starting in second column, I am entering =MPartsLOOKUP($A2,TestRng,2)

Sheet where data is looked up looks like this:

Key • Data1 • Data 2 • Data3
A1 • ABC • RF45 • opn
A1 • ABC • LF67 • opn
A1 • AB3 • LF67 • opn
BB2 • BCD • GP909 • wet2
BB2 • BCD • N54 • cls
CCC3 • CDE • PR1 • ptl
CCC3 • CDE • PR1 • ptl
CCC3 • CDE • PR2 • ptl

Any help very much appreciated. Please let me know if I need to clarify anything!

Thanks