Another Code Please!

[Dazzawm]

I need a code that when I select a column if a word i.e ABC or a number i.e 123 appears anywhere within a cell more than once I would like all duplicates removed just leaving the one.

e.g

Cell A1 Before

ABC 123 123 ABC ABC 456 DEF

Cell A1 After

ABC 123 456 DEF

and so on down every row until the last.

I will need to use this on more than one column so its best if the code works on the active column.

Thanks.

 

[MARK858]

Hi Rick, just to state what I have found by trying it on dazwm's original request and on his "2AD-FTV 2AD-FHV 2AD-FTV 2AD-FHV 2AD-FTV 2AD-FHV" posting is it is taking out all the instances of the first part of the string i.e. in

Const RemoveMe As String = "ABC,123" 'NOTE - no spaces around the commas

The 123 had 1 instance but the ABC was totally removed. I reversed the string and this time ABC remained but 123 was removed.

I got a different result to dazwm in the last post, I got 2AD-FHV.

No trouble with the hyphen but again the first part of the string was removed.

Hope this is of some help

 

[MARK858]

Rick, Sorry due to my slow typing my last statement referred to your first code, can see no problem when testing the 2nd version but haven't tested for case sensitivity
HTH

 

[Dazzawm]

Rick, Sorry due to my slow typing my last statement referred to your first code, can see no problem when testing the 2nd version but haven't tested for case sensitivity
HTH

I am glad you can get it to work!!

 

[MARK858]

dazwm, Ricks code is looking for duplicates of ABC and 123. If you asking does it look for random duplicates in the string (which I guessing you are) then no it doesn't

 

[Dazzawm]

dazwm, Ricks code is looking for duplicates of ABC and 123. If you asking does it look for random duplicates in the string (which I guessing you are) then no it doesn't

Yes I am. The duplicates could be anything. Basically anything seperated by a space if it is duplicated I want only one left. It could be like any of the following

ABC123
2AD-FHV
118.98
M118.988
4G13

and so on....

 

[Marcelo Branco]

I got it to work now but a small problem. Some of the data has dashes and it doesnt do it correct.

e.g

<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=290><COLGROUP><COL style="WIDTH: 218pt; mso-width-source: userset; mso-width-alt: 10605" width=290><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 218pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=17 width=290>2AD-FTV 2AD-FHV 2AD-FTV 2AD-FHV 2AD-FTV 2AD-FHV

becomes
<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=290><COLGROUP><COL style="WIDTH: 218pt; mso-width-source: userset; mso-width-alt: 10605" width=290><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 218pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17 width=290>2AD FTV FHV</TD></TR></TBODY></TABLE>

instead of 2AD-FTV 2AD-FHV


</TD></TR></TBODY></TABLE>

Change the pattern from
.Pattern = "\b\w+\b"

to
.Pattern = "\b\w+-?\w+\b"

To use in VBA instead of formula try this (select the entire column and run the macro below)

Sub useNoDups()
    Dim rng As Range, aCell As Range
    
    With Selection
        Set rng = Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1))
    End With
    
    For Each aCell In rng
        aCell = noDups(aCell.Text)
    Next aCell
    
End Sub

HTH

M.

 

[Dazzawm]

Change the pattern from
.Pattern = "\b\w+\b"

to
.Pattern = "\b\w+-?\w+\b"

To use in VBA instead of formula try this (select the entire column and run the macro below)

Sub useNoDups()
    Dim rng As Range, aCell As Range
 
    With Selection
        Set rng = Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1))
    End With
 
    For Each aCell In rng
        aCell = noDups(aCell.Text)
    Next aCell
 
End Sub

HTH

M.

Thanks Marcelo, that works but I need the pattern adjusted to cater for a . (dot) amongst data as well as a dash.

 

[Dazzawm]

Done it. Thanks everyone.

 

[Marcelo Branco]

Thanks Marcelo, that works but I need the pattern adjusted to cater for a . (dot) amongst data as well as a dash.

Change the pattern to
.Pattern = "\b\w+(-?|.?)\w+\b"

M.

 

[Marcelo Branco]

Done it. Thanks everyone.

I hadnt seen this one...

You are welcome and tks for the feedback

M.