A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dedupe Function works/works not



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2004, 07:49 PM
Tom Bock
external usenet poster
 
Posts: n/a
Default Dedupe Function works/works not

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







  #2  
Old March 16th, 2004, 11:36 PM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default Dedupe Function works/works not

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...b;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 Bock" wrote in message
...
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









  #3  
Old March 17th, 2004, 01:07 AM
Tom
external usenet poster
 
Posts: n/a
Default Dedupe Function works/works not

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" wrote in message
...
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...b;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 Bock" wrote in message
...
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











  #4  
Old March 17th, 2004, 09:15 AM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default Dedupe Function works/works not

Tom

If you are using Excel 2000 or above, perhaps:

arr = Split(target, ";")

Regards

Trevor


"Tom" wrote in message
...
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" wrote in message
...
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...b;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 Bock" wrote in message
...
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













  #5  
Old March 17th, 2004, 01:58 PM
Tom Bock
external usenet poster
 
Posts: n/a
Default Dedupe Function works/works not

Trevor:

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

Tom


"Trevor Shuttleworth" wrote in message
...
Tom

If you are using Excel 2000 or above, perhaps:

arr = Split(target, ";")

Regards

Trevor


"Tom" wrote in message
...
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" wrote in message
...
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...b;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 Bock" wrote in message
...
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















  #6  
Old March 17th, 2004, 09:41 PM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default Dedupe Function works/works not

Tom

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

Regards

Trevor


"Tom Bock" wrote in message
...
Trevor:

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

Tom


"Trevor Shuttleworth" wrote in message
...
Tom

If you are using Excel 2000 or above, perhaps:

arr = Split(target, ";")

Regards

Trevor


"Tom" wrote in message
...
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" wrote in message
...
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...b;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 Bock" wrote in message
...
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

















 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.