I have a column of data and i would like to remove the repeats in the same
column or maybe with a helper column. See below:

Example:

ColumnA ColumnB

John Doe John Doe
John Doe
Kim Smith Kim Smith
Kim Smith
Kim Smith
Kim Smith
Dav Hodge Dav Hodge
Dave Hodge

Thanks.

Hi,
This macro is thanks to CPearson

Public Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString
explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns( 1), vbNullString)
1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub

Hope this helps

"Steve C" wrote:

I have a column of data and i would like to remove the repeats in the same
column or maybe with a helper column. See below:

Example:

ColumnA ColumnB

John Doe John Doe
John Doe
Kim Smith Kim Smith
Kim Smith
Kim Smith
Kim Smith
Dav Hodge Dav Hodge
Dave Hodge

Thanks.

Try this:

1) Make sure that the first row in the column has a column header.
2) On the Data menu, point to Filter, and then click Advanced Filter.
3) In the Advanced Filter dialog box, click Copy to another location.
4) If the column (or range) that you are evaluating is not already selected,
delete any information in the List range box and then click the column (or
select the range) that contains your data.
5) In the Copy to box, delete any information in the box or click in the
box, and then click a blank column where you want to copy the unique values.
6) Select the Unique records only check box, and click OK.
7) The unique values from the selected range are copied to the new column.

Then you can delete the first column and keep only the column with the
unique names.

Paula

"Steve C" wrote:

I have a column of data and i would like to remove the repeats in the same
column or maybe with a helper column. See below:

Example:

ColumnA ColumnB

John Doe John Doe
John Doe
Kim Smith Kim Smith
Kim Smith
Kim Smith
Kim Smith
Dav Hodge Dav Hodge
Dave Hodge

Thanks.

Thanks. This was helpful. is there a way to accomplish this without runing
the macro?

"Eduardo" wrote:

Hi,
This macro is thanks to CPearson

Public Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString
explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns( 1), vbNullString)
1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub

Hope this helps

"Steve C" wrote:

I have a column of data and i would like to remove the repeats in the same
column or maybe with a helper column. See below:

Example:

ColumnA ColumnB

John Doe John Doe
John Doe
Kim Smith Kim Smith
Kim Smith
Kim Smith
Kim Smith
Dav Hodge Dav Hodge
Dave Hodge

Thanks.

Thanks Paula, i'm familiar with the unique records function. I'm looking to
replace the repeats in the column with a blank space to maintain the current
cell positions. Is there a formula that can do that? Thanks!

"Paula" wrote:

Try this:

1) Make sure that the first row in the column has a column header.
2) On the Data menu, point to Filter, and then click Advanced Filter.
3) In the Advanced Filter dialog box, click Copy to another location.
4) If the column (or range) that you are evaluating is not already selected,
delete any information in the List range box and then click the column (or
select the range) that contains your data.
5) In the Copy to box, delete any information in the box or click in the
box, and then click a blank column where you want to copy the unique values.
6) Select the Unique records only check box, and click OK.
7) The unique values from the selected range are copied to the new column.

Then you can delete the first column and keep only the column with the
unique names.

Paula

"Steve C" wrote:

I have a column of data and i would like to remove the repeats in the same
column or maybe with a helper column. See below:

Example:

ColumnA ColumnB

John Doe John Doe
John Doe
Kim Smith Kim Smith
Kim Smith
Kim Smith
Kim Smith
Dav Hodge Dav Hodge
Dave Hodge

Thanks.