-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
elimating unique values in a column
- Thread starter jrwilmott
- Start date
Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
Hi,
I've found several solutions to this before by doing a search on this forum. There are posts on macro's that can do this.
Most often than not I create a countif that counts down the column the number of occurences of items. See below: I then filter the duplicates and delete the lines.
I've found several solutions to this before by doing a search on this forum. There are posts on macro's that can do this.
Most often than not I create a countif that counts down the column the number of occurences of items. See below: I then filter the duplicates and delete the lines.
Upvote
0
Domski
Well-known Member
- Joined
- Jan 18, 2005
- Messages
- 7,292
I find Advanced Filter to be the best way to get rid of duplicates.
Use the following settings:
Action - Copy to another location
List range - the data to be de-duplicated
Criteria range - leave blank
Copy to - where you want the filtered data to appear (has to be on the same sheet)
Unique records only - Yes (ticked)
Dom
Use the following settings:
Action - Copy to another location
List range - the data to be de-duplicated
Criteria range - leave blank
Copy to - where you want the filtered data to appear (has to be on the same sheet)
Unique records only - Yes (ticked)
Dom
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
jrwilmott said:
Do you mean to get from:
bob
bob
carla
dennis
bob
carla
the list:
bob
carla
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
jrwilmott said:
How is this different from the question I posed?
Upvote
0
NateO
Legend
- Joined
- Feb 17, 2002
- Messages
- 9,700
Here's one way:
Your workbook would need to be saved prior to running this.
Code:
Sub DupTime()
Dim cn As Object, rs As Object
Dim clcMde As Long
With Application
clcMde = .Calculation
.ScreenUpdating = False: .Calculation = xlCalculationManual
End With
With Sheets(1)
.Rows("1:1").Insert
.Range("A1").Value = "col1"
End With
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
Set rs = CreateObject("ADODB.Recordset")
With rs
.Open "Select col1 From [Sheet1$a:a] group by col1 having Count(col1)>1", cn, 3, 3
Sheets(1).[b2].CopyFromRecordset rs
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
Sheets(1).Rows("1:1").Delete
With Application
.Calculation = clcMde: .ScreenUpdating = True
End With
End Sub
Upvote
0
Similar threads
- Solved
- Question
- Question
