After some data cleanup (concatenating cells from different worksheets), I
often end up with duplicate values.

For instance, a cell may contain "richmond; atlanta; richmond; san diego;
atlanta; atlanta"

To eliminate the duplication, I execute the "dedupe" function below (its
code is between the ***s) and the cell value is now "richmond; atlanta; san
diego".

=dedupe(A1)

*************************************************
Function dedupe(target As String)
Dim nodupes As New Collection
Dim arr As Variant
arr = Evaluate("={""" & WorksheetFunction.Substitute(target, ";", """,""")
& """}")
On Error Resume Next
For Each ar In arr
nodupes.Add Item:=Trim(ar), key:=CStr(Trim(ar))
Next ar
On Error GoTo 0
first = True
For Each ar In nodupes
If first Then
dedupe = ar
first = False
Else
dedupe = dedupe & "; " & ar
End If
Next ar
End Function
*************************************************


So far, this function is working fine; however, we're now doing cleanup on
some "real data". The text in the
cells can be fairly extensive (eventually will be transferred into a
database).

At this time, the "working function" does not work any longer.... I have no
idea why? Below is an example of a
lengthy cell content.

Does anyone know why it wouldn't work now??? Actually, the function does
not give any text (cell is now completely empty)

Thanks!

Tom


Actual Data Example:
====================

Attend FPC; Attend FPC; Attend FPC; Attend FPC; Attend IPC, MPC; Attend IPC,
MPC; Attend IPC, MPC; Attend IPC, MPC; Attend training workshops; Collect
records and data; Collect records and data; Collect records and data;
Collect records and data; Conduct analysis of ALLIED ACTION 04; Conduct
analysis of ALLIED WARRIOR; Conduct analysis of BLUE GAME 04; Conduct
analysis of DESTINED GLORY; Conduct analysis of exercises, training and CDE;
Deploy for exercise; Deploy for exercise; Deploy for exercise; Deploy for
exercise; Develop Analysis Plan; Develop Analysis Plan; Develop Analysis
Plan; Develop Analysis Plan; Produce EXPI/EXOPORD inputs; Produce
EXPI/EXOPORD inputs; Produce EXPI/EXOPORD inputs; Produce EXPI/EXOPORD
inputs; Publish analysis report; Publish analysis report; Publish analysis
report; Publish analysis report; Reconstruct events; Reconstruct events;
Reconstruct events; Reconstruct events; Report to the OSE and to the Lessons
Learned Division and Remedial Action Process; Report to the OSE and to the
Lessons Learned Division and Remedial Action Process; Report to the OSE and
to the Lessons Learned Division and Remedial Action Process; Report to the
OSE and to the Lessons Learned Division and Remedial Action Process