Duplicate Rows: Delete Both Duplicate rows

[bamundsen]

I have a spreadsheet which contains two columns, make and model. I need to delete all duplicate rows. I have searched the forum, but have only found examples of deleting or flagging only one of the duplicate rows.
Spreadsheet example:

make model
==== =====
chev camaro
chev camaro
chev cavalier

I want to remove both rows containing chev camaro. Thanks,

Brett

 

[gizmo1973]

You could concatanate the data

i.e.
=a2&" "&b2 this would then give you "chev camaro" in one cell (if your data is in columns A and B if not ammend as ness) and then use my FAQ faq68-5446 to flag the duplictates and then delete

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[bamundsen]

gizmo1973:

Thx for the reply. I think I need to provide more info for you. I want to remove the dup row and the orignal row. So, if there are two "chev camaro" rows, I want both rows deleted. Is there a way to flag both rows that are dup'd?Make sense?

Thanks again for your reply.

Brett

 

[bkpchs237]

bamundsen,
What exactly amounts to a "duplicate?" In your example above I'll assume that a duplicate model alone constitutes a duplicate. If so, you could place a counting formula in an adjacent column with the formula: =countif($B:$B,$A2) and copy it down the entire column. Modify your counting formula if you need more criteria.
Then use the AutoFilter on the counting column and select only the number 1 (the unique values. Then select or highlight all the data you need that you see on the spreadsheet. Then click Edit, GoTo, Special, Visible Cells Only, OK. Then click Edit, Copy and go to a new worksheet (or somewhere below and to the right of any of the data on this worksheet) and click Edit, Paste. Then delete the original data set. This performs the action you desire just in a different fashion.
Of course you could use the AutoFilter in the counting column and select the multiple items one by one, highlight the values and select Edit, Delete, Entire Row, but the first option is easier if not more elegant.
Hope this helps.

 

[gizmo1973]

If I'd have been awake last night I would have said what bkpchs237 said anyway!

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[asrisk]

Try running this macro. It will delete all duplicate rows, whether they are next to each other or not (You don't say whether this is a criteria).

ASSUMPTIONS: This assumes your data starts on Row 1, is in Columns A and B, and that there is a blank row at the end of the list. Please adjust accordingly.

Sub DeleteDups()

Dim Row1 As Integer
Dim Row2 As Integer
Dim FoundDup As Boolean

'start at first row of list
Row1 = 1

'take each row, and compare it to all the other rows in the list.
Do
    'start scanning at next row
    Row2 = Row1 + 1
    FoundDup = False
    
    Do
        'Scan down list for duplicates, deleting any you find
        If Cells(Row1, 1) = Cells(Row2, 1) And Cells(Row1, 2) = Cells(Row2, 2) Then
            Cells(Row2, 1).EntireRow.Delete
            FoundDup = True
        Else
            Row2 = Row2 + 1
        End If
    Loop Until Cells(Row2, 1) = ""
    'if there were duplicates then delete the original too.
    If FoundDup Then
        Cells(Row1, 1).EntireRow.Delete
    Else
        Row1 = Row1 + 1
    End If
'on to the next one.
Loop Until Cells(Row1, 1) = ""

End Sub

Hope this helps!

-------
^{I am not responsible for any "Sponsored Links" which may appear in my messages.}