Formula to Delete Duplicates but keep one record
 

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!

Formula to Delete Duplicates but keep one record
 

Posting your code might help us help you alittle more but this might do.

Sub RemoveDuplicates()
Dim whatColumn As Integer
Dim lr As Long
whatColumn = 1 '1 = A
lr = ActiveSheet.Cells(Rows.Count, whatColumn).End(xlUp).Row
ActiveSheet.Range("$A$1:$E$" & lr)_
.RemoveDuplicates Columns:=whatColumn, Header:=xlYes
End Sub

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!

Formula to Delete Duplicates but keep one record
 

Hi,

Macros have no problem with filters. However, here is code to remove
duplicated:

in this case my duplicate data is in A1:A1111

Sub RemoveDuplicates()
Dim Bottom As Long
Columns("B:B").Insert
Bottom = [A1].End(xlDown).Row
Range("B1:B" & Bottom).Select
Selection = "=1/(COUNTIF(R1C[-1]:RC[-1],RC[-1])1)"
Selection.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Columns("B:B").Delete
End Sub

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!

Formula to Delete Duplicates but keep one record
 

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!



IF(A1=A2,"duplicate","ok")

Filter the Column.Select Duplicate and Delete the Row

Hardeep kanwar

Formula to Delete Duplicates but keep one record
 

The formula I have using is =IF(COUNTIF($C$9:C9,C9)1,"a",1). Then I go
through and filter the "a". I delete all of the "a" and then remove the
filter. When I recorded this in the Macro it did not run properly on a new
spreadsheet. Any ideas why? Is there something I need to do differently?
I'm not familar with Visual Basic so I'm not sure what I did wrong. Thanks.

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!