12

I have an Excel sheet which contains duplicate rows.

I want to remove a row if its values in columns A C D E F are same as another row's values in those columns (ignore column B while calculating duplicates, but remove it while removing a row).

At the moment it ignores B in comparison as well as deletion.

Destroy666
  • 12,350
user33949
  • 429

4 Answers4

19

In Excel 2013

  1. Select all Data
  2. Select Data Tab -> Remove duplicates
  3. Check 'My columns have headers' if your data has column headers.
  4. Uncheck all columns you would NOT like to use in comparison
  5. Press OK

Done

Community
  • 1
user33949
  • 429
1

If I understood you correctly, given rows 1 and 2, you want to delete a row 2 if and only if A1 = A2, C1 = C2, D1 = D2, E1 = E2 and F1 = F2.

This is what I came up with. It could probably be shorter but it does the trick:

Sub DeleteDuplicate()
Dim current As String
ActiveSheet.Range("A1").Activate
Do While ActiveCell.Value <> ""
    current = ActiveCell.Address
    ActiveCell.Offset(1, 0).Activate
    Do While ActiveCell.Value <> ""
        If ((ActiveSheet.Range(current).Value = ActiveCell.Value) And (ActiveSheet.Range(current).Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value) And (ActiveSheet.Range(current).Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value) And (ActiveSheet.Range(current).Offset(0, 4).Value = ActiveCell.Offset(0, 4).Value) And (ActiveSheet.Range(current).Offset(0, 5).Value = ActiveCell.Offset(0, 5).Value)) Then
            ActiveSheet.Rows(ActiveCell.Row).Delete
        Else
        ActiveCell.Offset(1, 0).Activate
        End If
    Loop
    ActiveSheet.Range(current).Offset(1, 0).Activate
Loop
End Sub

What this does is loop through all remaining lines using the ActiveCell as a pointer to the Row being evaluated, and storing the "original" Row's Cell in the current variable. Once the loop ends, the Cell below the current is activated and the inner evaluation loop starts again.

If I messed up somewhere don't hesitate to tell me :)

kenkh
  • 329
1

Add this formula into each row, eg; in column G:

=IF(AND(A2=C2,A2=D2,A2=E2,A2=F2),"DELETE ME","")

This will give a result like:

A   B   C   D   D   F   G
-   -   -   -   -   -   -
x   o   x   x   x   x   DELETE ME
x   x   x   x   x   o   
x   x   x   x   x   x   DELETE ME
x   o   o   x   x   x

Then create a filter on Column G for rows which say 'DELETE ME', and delete them.

Garrulinae
  • 1,939
  • 16
  • 26
0

@ Garrulinae

I used your idea as an inspiration. I had a lot of rows and what I wanted was to delete the rows containing duplicate values of a column. If I use Remove duplicates over that column then it will remove only data from that column and not the entire row. I wanted to delete every 2nd and 3rd row from that entire table. So, what i did I made a new column and left the first row's value empty put delete me in second and third row. And then i copied values of first three rows of that particular column only (not entire row) and pasted over the whole column of the table. Now, I had "delete me" in every 2nd and 3rd row of the entire column. Then i just have to sort the values of that column and delete all the rows containing delete me. You can use the same solution if you want delete every 2nd, 3rd and 4th or 2nd, 3rd,4th and 5th or so on...

Hope it helps someone....

Sunil
  • 1