Deleting duplicated rows based on data in two columns
 

I have a range of data in cells A1:K1000 and some of the row data in that
range has been partially duplicated. What I would like to do is to delete
the duplicated row if the cell values in column A are the same as well as the
data in Column E.

For example:
If cell A1 (Smith) and A2 (Smith) are the same and cells E1 (Microsoft) and
E2 (Microsoft) are the same, delete row 2. However If cell A1 (Smith) and A2
(Smith) are the same and cells E1 (Microsoft) and E2 (Apple) are different,
then this is not a duplicate so it should not be deleted.

Any assistance would be appreciated.

Deleting duplicated rows based on data in two columns
 

Try this:

========
Sub DeleteDupes()
'JBeaucaire (11/16/2009)
Dim LR As Long, i As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
If Cells(i, "A") = Cells(i - 1, "A") And _
Cells(i, "E") = Cells(i - 1, "E") Then _
Rows(i).Delete xlShiftUp
End If
Next i

Application.ScreenUpdating = True
End Sub
========

Does that help?

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Monk" wrote:

I have a range of data in cells A1:K1000 and some of the row data in that
range has been partially duplicated. What I would like to do is to delete
the duplicated row if the cell values in column A are the same as well as the
data in Column E.

For example:
If cell A1 (Smith) and A2 (Smith) are the same and cells E1 (Microsoft) and
E2 (Microsoft) are the same, delete row 2. However If cell A1 (Smith) and A2
(Smith) are the same and cells E1 (Microsoft) and E2 (Apple) are different,
then this is not a duplicate so it should not be deleted.

Any assistance would be appreciated.

Deleting duplicated rows based on data in two columns
 

Hi

Easier approach to do this - -

DataFilterAdvanced Filter --select Unique records only

This will filter the unique records.. then u can select copy to another
location

Regards
Om

"JBeaucaire" wrote:

Try this:

========
Sub DeleteDupes()
'JBeaucaire (11/16/2009)
Dim LR As Long, i As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
If Cells(i, "A") = Cells(i - 1, "A") And _
Cells(i, "E") = Cells(i - 1, "E") Then _
Rows(i).Delete xlShiftUp
End If
Next i

Application.ScreenUpdating = True
End Sub
========

Does that help?

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Monk" wrote:

I have a range of data in cells A1:K1000 and some of the row data in that
range has been partially duplicated. What I would like to do is to delete
the duplicated row if the cell values in column A are the same as well as the
data in Column E.

For example:
If cell A1 (Smith) and A2 (Smith) are the same and cells E1 (Microsoft) and
E2 (Microsoft) are the same, delete row 2. However If cell A1 (Smith) and A2
(Smith) are the same and cells E1 (Microsoft) and E2 (Apple) are different,
then this is not a duplicate so it should not be deleted.

Any assistance would be appreciated.