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.

2
  • Can you add a screen shot with a before and desired after? I'm struggling to picture what you need (sorry)
    – Dave
    Mar 13, 2014 at 15:43
  • So any duplicate values in columns A,C,D,E,F will result in an entire row deletion, keeping the first instance of the duplicate? What if A1 matches A2, but C2 matches C3, does row 2 get deleted? Mar 13, 2014 at 15:48

4 Answers 4

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

6
  • No, this looks for rows which are similar to other rows. I also thought of this, but it's a solution to a different question :)
    – Garrulinae
    Mar 14, 2014 at 7:31
  • @Garrulinae What are you saying, can you explain how this doesn't work? It seems correct answer to OP's question. Aug 4, 2016 at 1:37
  • @TheCrazyProgrammer - Looking at this over 2 years later, I can see that the OP's question "I want to remove a row if its A C D E F columns are same" can be interpreted in two different ways. I took it to mean that if these columns are the same as each other within this row. This answer assumes that it means if the values in the columns in this row are the same as another row. In hindsight, this assumption appears to have been correct, especially since it has been marked as the correct answer.
    – Garrulinae
    Aug 4, 2016 at 8:23
  • @Garrulinae Gotcha. Now I saw and understood your answer. On a side note, I am surprised that functionality required by OP (and me) exists as a straightforward button in Excel, but googling gives you a bunch of articles doing complicated things which for a moment made me doubt this answer. Aug 4, 2016 at 15:59
  • @TheCrazyProgrammer, I have a side note too - I've only just realised that it was the OP who posted this answer! It's obviously the correct answer, but it's a shame the question wasn't more clear.
    – Garrulinae
    Aug 5, 2016 at 0:39
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 :)

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.

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....

1
  • 2
    I don't fully understand what you're saying.  Instead of describing what you did, can you show us what formula(s) you used, and present some example data, and show how your answer works on that data? Nov 28, 2015 at 1:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .