How do I remove Duplicate rows?

[Guest]

I have an excel spreadsheet that has 2 columns. Col A is customer# and Col B
is invoice #.
A customer# can have many invoice#s
A customer# can have many rows with the same inv#.
An invoice# can only be associated with 1 customer#

I need to create a spreadsheet that shows each customer with the associated
invoice#. Each customer#/invoice# should only show once.

for example:

cust# invoice#
1 100
1 100
1 101
2 200
3 300
3 300


The result would be:

cust# inv#
1 100
1 101
2 200
3 300

How do I get rid of the extra rows?

 

[Tom Ogilvy]

One method might be to use a pivot table report under the Data menu.

also see Chip Pearson's page on duplicates and uniques:
http://www.cpearson.com/excel/duplicat.htm

 

[Guest]

Here is a macro solution, assuming that row 1 has headers.
Right click on any worksheet (tab) at the bottom of your workbook.
Select View Code.
Go to Insert>Module and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to Tools>Macro>Macros... and run the macro.
Be sure to save a copy of your workbook beforehand.

---------------------------------------------------

Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Range("A65536").End(xlUp).Row
Range("A1:B" & Numrows).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") + Cells(Iloop, "B") = Cells(Iloop - 1, "A") + _
Cells(Iloop - 1, "B") Then
Rows(Iloop).Delete
End If
Next Iloop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Guest]

Thanks! It worked!

Here is a macro solution, assuming that row 1 has headers.
Right click on any worksheet (tab) at the bottom of your workbook.
Select View Code.
Go to Insert>Module and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to Tools>Macro>Macros... and run the macro.
Be sure to save a copy of your workbook beforehand.

---------------------------------------------------

Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Range("A65536").End(xlUp).Row
Range("A1:B" & Numrows).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") + Cells(Iloop, "B") = Cells(Iloop - 1, "A") + _
Cells(Iloop - 1, "B") Then
Rows(Iloop).Delete
End If
Next Iloop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Guest]

Hello Ken,

I am Lost in Alabama and I have a question about this post as my situation
is different than the orginal post.

How would I modify this code if the duplicate data is by the entire row. In
other words the duplicates will be in varing columns.

Thanks,

Lost In Alabama

 

[Ardus Petus]

Could you specify on how many (or which) columms you want to check for
duplicates ?
There HAS to be a limit on the number of columns you want to check.

Such as it is, your macro will only detect duplicates on rows A & B

Cheers,

 

[Guest]

I need to check from A:R.

Thanks,

Lost In ALabama

 

[Ardus Petus]

Here is the code (not fully tested)

HTH

--
AP

'----------------------------------
Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer
Dim lignesEgales As Boolean
Dim ligneRange As Range
Dim c As Range
'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1", Cells(Numrows, "B")).Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
Set ligneRange = Range("A1:R1").Offset(Iloop - 1, 0)
lignesEgales = True
For Each c In ligneRange
If c.Value <> c.Offset(-1, 0).Value Then
lignesEgales = False
Exit For
End If
Next c
If lignesEgales Then Rows(Iloop).Delete
Next Iloop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'----------------------------------

 

[Guest]

Ardus,

Thank you for this code, however, when I tested it, it deletes random cells
of duplicate data which messes up the data in relation to the row. I need it
to look at the data as a row and delete the entire row if it is a duplicate
row. Also, the duplicates may not be side-by-side.

Thanks for your help!

Lost

 

[Ardus Petus]

I Tested it more thoroughly, everything seems OK

Could you post me some sample data with which the macro "messes up" the data
?

Yours,

 

[Guest]

I think the problem is the fact that I cannot sort my data without lossing
the original order of the data and therefore, the duplicate rows are not
side-byside. I altered the code as follows, and it will delete duplicate
rows if they are side-by-side, but will not delete the duplicates if they are
separated. I need to delete the duplicate rows that are side-by-side and the
ones that are separated.

Dim Iloop As Integer
Dim Numrows As Integer
Dim lignesEgales As Boolean
Dim ligneRange As Range
Dim c As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Cells(Rows.Count, "A").End(xlUp).Row
For Iloop = Numrows To 2 Step -1
Set ligneRange = Range("A1:R1").Offset(Iloop - 1, 0)
lignesEgales = True
For Each c In ligneRange
If c.Value <> c.Offset(-1, 0).Value Then
lignesEgales = False
Exit For
End If
Next c
If lignesEgales Then Rows(Iloop).Delete
Next Iloop

Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[Guest]

Here is a macro solution, assuming that row 1 has headers.
Right click on any worksheet (tab) at the bottom of your workbook.
Select View Code.
Go to Insert>Module and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to Tools>Macro>Macros... and run the macro.
Be sure to save a copy of your workbook beforehand.

---------------------------------------------------

Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Range("A65536").End(xlUp).Row
Range("A1:B" & Numrows).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") + Cells(Iloop, "B") = Cells(Iloop - 1, "A") + _
Cells(Iloop - 1, "B") Then
Rows(Iloop).Delete
End If
Next Iloop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

 

[KH_GS]

Hi Ken

How do I edit the code so that the macro for deleting duplicates to b
more dynamic such that it is performed down the column from the cel
that I select instead of fixing it to perform on column A?

 

[Guest]

Terry,

Thank you so much for sharing this information.

Lost

 

[Guest]

i have a simular situation except i need it to only look for duplicates in
column A under the account# and only keep the one that has the highest amount
in column E
my list looks like the following

account# account name state catagory $Amount due
1 john ca 5 $
500.00
1 john ca 5 $
1000.00
1 john ca 5 $
5000.00

 

[Guest]

Ken I have a similar problem. The data list is as follows.

Job Code
Allen 101
Caves 105
Hasson 960
Allen 101
Mclean 101
Allen 102

It should simplify to

Job Code
Allen 101
Caves 105
Hasson 960
McLean 101
Allen 102

This is all dynamically changing data so all Job names and Codes refer to
cells on different worksheets. If this data is changed on those other
worksheets this list should expand and contract dynamically as well. Is this
possible?

Please help.

Howard Leung