Rename Duplicates

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
I searched on the board for duplicates and could not find what I needed.

How could I create a macro to rename the duplicate cells in a column and delete other cells that is not duplicate?

Is subtotal the only way to do this kinda job? Thank you all.
Book2
ABCD
1NameCount
2IXC2
3BAS1
4TGB1
5WAV5
6BAS3
7IDT3
8ITA9
9FRA8
10QWE8
11FRA7
12IDT6
13ITA13
14TGB2
15
16NameCount
17IXC
18BASYes
19TGBYes
20WAV
21BASYes
22IDTYes
23ITA
24FRAYes
25QWEYes
26FRA
27IDT
28ITA
29TGB
Sheet1
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
dont really understand what you wnat to do, are you wanting to delete the UNIQUE entries,
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

to delete all uniques
Code:
Option Explicit

Sub filter_duplicates()
'Erik Van Geit
'060317
'get list with data which appear more then once
'= uniques(range - uniques(range))
'no header needed
'autofilter with extra column would do the same
'START WITH
'a1 a2 a3 a4 a1 a1 a2 a2 a5 a6 a6 (in a column!)
'TO GET
'a1 a1 a1 a2 a2 a2 a6 a6

Dim LR As Long
Dim RNG As Range

Const CC As Integer = 1 'check column
Const FR As Long = 1    'first row with data

LR = Cells(Rows.Count, CC).End(xlUp).Row
Set RNG = Range(Cells(FR, CC), Cells(LR, CC))

'ActiveSheet.Copy
Application.ScreenUpdating = False
    Columns(CC).Insert
    With RNG.Offset(0, -1)
    .Formula = "=IF(COUNTIF(" & RNG.Address & "," & RNG(1).Address(0, 0) & ")>1,1,"""")"
    .Value = .Value
    On Error Resume Next
    .Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True '.Delete
    On Error GoTo 0
    End With
    Columns(CC).Delete
Application.ScreenUpdating = True
End Sub
can you explain what you mean by "rename" ?

kind regards,
Erik
EDIT: formula tweaking
2. changed wrongly edited formula :)
3. updated comments
 

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
Yes, I want to delete the unique entries and rename all the duplicates into something else than the duplicate values themselves.

Thank you!!
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001

ADVERTISEMENT

is your list sorted, i.e are all the duplicated entries together, and what do you mean by rename the duplicates, if you rename them then they wont be duplicates and the code would surely delete them too, i dont know

we would require more information on the renaming structure.
 

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
Yes the list is sorted. so the duplicate values will appear consecutively. Sorry if I may not have clearly stated what I wanted to do...

I want to delete the unique cells from a column. After deleting all unique cells the rest of the cells are named as "Yes" or anything..

I hope I cleared my question. Thank you all for your attention.
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001

ADVERTISEMENT

is your list sorted, i.e are all the duplicated entries together, and what do you mean by rename the duplicates, if you rename them then they wont be duplicates and the code would surely delete them too, i dont know

we would require more information on the renaming structure.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
seems like you didn't see my code to delete the uniques
what would be the logic to rename the duplicates ?
 

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
another way to mark duplicates is to use

[code[=IF(COUNTIF(A$1:A$11,A1)>1,"Duplicate","")[/code]

where A$1:A$11 is your range of letters,

lets say your range is A$1:A$11 enter the above formula in to cell B1 and fill down, the word Duplicate will appear in all cells next to the duplicate, now select column B, click copy, then paste special values, now reselect column B press F5 and click special, select blanks, this will select the blanks (unique entries) now press CTRL + - to delete the rows,
 

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
Thank you Erik,

I tried your codes and it hides all duplicates. I need to delete unique cells in a column.

what would be the logic to rename the duplicates ?
After deleting all uniques, anything that appears in the column should be name to "Yes". Actually there is no logic since want to see where the duplicates are.

Hope it helps. Thank you.
 

Forum statistics

Threads
1,136,309
Messages
5,674,999
Members
419,541
Latest member
freddyboots

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