Rename Duplicate Cells

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
544
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,540
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

Well-known Member
Joined
Dec 27, 2008
Messages
544
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Try:-
Code:
[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

Well-known Member
Joined
Dec 27, 2008
Messages
544

ADVERTISEMENT

That worked

Thank you very much
 

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
544
One last question instead of changing the first duplicate item can the macro change the second item.

Thank you
 

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
544

ADVERTISEMENT

Any thoughts
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, This code should change the second Duplicate, where ever it is, and even if there are more than 2 duplicates.
Code:
[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
 

ELNERO

New Member
Joined
Jun 15, 2019
Messages
2
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,
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,916
Messages
5,834,363
Members
430,279
Latest member
mkasledge1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top