Deleting Duplicate rows in excel 1

[mooneye]

Is there anyway of deleting duplicate rows in an excel spreadsheet. I have a very large spreadsheet that would take too long to do manually.

 

[flashb]

quickest way I know without using VBA would be to do the following with a set of figures in column A:

1. sort them (so the duplicates are next to each other)

2. use some formula in a spare column such as

=if(a2=a1,"*",0)in row 2
=if(a3=a2,"*",0)in row 3 and copy down to the bottom of your dataset.

this identifies the duplicates with a "*".

3. Copy & Paste Special the column with the "*" in

4. Sort by the "*" column. (this will put all the unnecessary duplicates together.

5. Delete all rows with a "*" in.

 

[xlhelp]

Here is a subroutine taken from Chip Pearson:

DeleteDuplicateRows

This macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g., C2:C99. To determine whether a row has duplicates, the values in the selected column are compared. Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in the active column, the first row remains, and all subsequent rows are deleted.

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

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

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[DaleWatson123321]

Another option...

Here's a routine that "automatically" deletes duplicate rows. (Nothing "manual" involved)

Sub Delete_Duplicates()
Application.ScreenUpdating = False
Application.Goto Reference:="Phone_Column"
ActiveCell.Offset(1, 0).Activate
Do
curcell = ActiveCell.Value
If curcell = "" Then Exit Sub
Range("inp&quot.Value = curcell
num = Range("dbnum&quot.Value
If num > 1 Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

This routine utilizes the DCOUNTA function. The easiest way to set this up - and understand more about Excel's POWERFUL database functions, is for me to send you the file.

If you would like the file, just email me, and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca