Dedupe Function works/works not

[Tom Bock]

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

 

[Trevor Shuttleworth]

Tom

when the function fails, you are getting Error 2015 from the line:

arr = Evaluate("={""" & WorksheetFunction.Substitute(target, ";", """,""") &
"""}")

With the sample record you provided, I tried building up the length of data
character by character and it failed at 226 characters -

"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; Colle"

This produced:

"Attend FPC; Attend IPC, MPC; Attend training workshops; Collect records and
data; Colle"

which is actually 87 characters long with 4 semicolons (

Moving "along" the data by 222 characters selected:

"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"

238 characters with 6 semicolons. These elements are all unique and
"dedupe" to the same thing:

"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"

There is a Microsoft Knowledge Base entry
http://support.microsoft.com/default.aspx?scid=kb;en-us;211601

In Microsoft Excel 2000, if you use the Evaluate method in a Visual Basic
for Applications macro to evaluate a statement or formula that contains a
date, the method may return either of the following error messages:



Run-time error '13': Type mismatch

-or-

Error 2015



Not exactly your problem but maybe too close for comfort ?



Sorry I can't see a pattern or offer any suggestions. Any other way to
populate the array/collection ?



Regards



Trevor

 

[Tom]

Trevor:

Thanks for the reply... at least I know now that I'm not doing some wrong.
I appreciate your stepping through this process and providing me such
detailed feedback.

I'll look into other ways as to how we can clean the data up.

Again, thanks!!!

Tom

 

[Trevor Shuttleworth]

Tom

If you are using Excel 2000 or above, perhaps:

arr = Split(target, ";")

Regards

Trevor

 

[Tom Bock]

Trevor:

Simply incredible!!! Thank you so much... that works great!!!

Tom

 

[Trevor Shuttleworth]

Tom

you're welcome. Great to know it works for you.

Regards

Trevor