On Mon, 18 May 2009 02:01:54 -0700 (PDT), Randy
wrote:

Thanks to all the MVPs and other knowledgeable people on this group. I
hope to someday become half as proficient. I’m having some trouble at
the moment and wonder if anyone has a snippet of code or other
solution to this pesky little issue …

I have cell data, arranged in rows spanning a yet unknown number of
rows (but arranged entirely in column A), that each contain two
instances of a phrase. Each cell generally has a unique set of phrases
(as opposed to other cells, that is), but not so in all cases. There
could be an instance where for example A50 might have the same two
phrases as that in A2.

I would like to eliminate the second instance of the phrase in each
row cell (in the cells spanning Column A, that is). The difficulty
lies in that the first letter of the second instance of the phrase is
always joined with the last word in the first instance.

Three example cells (exactly as encountered in my list):

I General principlesI General principles
3 Miscellaneous3 Miscellaneous
a Between reviewa Between review

Does anyone have any idea as to what I might do in order to make the
following happen:

1) Discard one instance of the phrase in each row cell, and
2) Keep only one instance of the phrase found in each row cell, and
3) Paste the one instance to column B, alongside its corresponding
cell in column A.


Any suggestions are sincerely appreciated.

thanks,

Randy

This can be done with a VBA macro.

As written, it requires that there be an exact duplicate. In other words, if
one of your strings has a trailing space (or any other character) it will not
be seen as having a duplicate (this can be easily changed, if you have more
specifications).

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro
by name, and <RUN.

====================================
Option Explicit
Sub RemDup()
Dim c As Range, rg As Range
Dim LastRow As Long
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(.*)(\1)$"

'rg set to A1:last row containing data in col A
'could be set to other ranges

Set rg = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

For Each c In rg
Set mc = re.Execute(c.Value)
If mc.Count 0 Then
c.Offset(0, 1).Value = mc(0).submatches(1)
Else
c.Offset(0, 1).Value = ""
End If
Next c
End Sub
===============================

--ron