Rename Duplicate Cells

[Mldeuser]

Is it possible to have a macro compare the contents in column "C" and were it finds a duplicate entry the macro would add to the first entry "-Free".

For example

CB1270 - Free
CB1270

In the above example the macro would add the "-Free" to CB1270.

The duplicate entries are next to each other.

Thank you

 

[Kaps_mr2]

do you really need a macro ? the following formulae puts free in the adjacent column :=-

=IF(COUNTIF($D$4:D4,D4)=1,"free","")

thanks

Kaps

 

[Mldeuser]

The reason for wanting a macro to do this is the sheet in question is a download from the host system and the formulas would be deleted each time the report was downloaded.

Thank you

 

[MickG]

Hi, Try:-

[COLOR="Navy"]Sub[/COLOR] MG10Aug00
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("c1"), Range("c" & Rows.count).End(xlUp))

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                .Add Dn.Value, Dn.address
            [COLOR="Navy"]Else[/COLOR]
                Range(.Item(Dn.Value)) = Range(.Item(Dn.Value)) & "- Free"
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mldeuser]

That worked

Thank you very much

 

[Mldeuser]

One last question instead of changing the first duplicate item can the macro change the second item.

Thank you

 

[Mldeuser]

Any thoughts

 

[MickG]

Hi, This code should change the second Duplicate, where ever it is, and even if there are more than 2 duplicates.

[COLOR="Navy"]Sub[/COLOR] MG11Aug40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("c1"), Range("c" & Rows.count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                .Add Dn.Value, 0
            [COLOR="Navy"]Else[/COLOR]
               [COLOR="Navy"]If[/COLOR] .Item(Dn.Value) = 0 [COLOR="Navy"]Then[/COLOR]
                    .Item(Dn.Value) = .Item(Dn.Value) + 1
                     Dn.Value = Dn.Value & " -Free"
               [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mldeuser]

Once again

Thank you

 

[ELNERO]

Hello,

I now this thread is kind of old but is it possible to modify this such that it effectively counts upwards. Basically so that for first instance I can add ".01" for second instance I can add ".02" and so on?

Thanks,