How do I remove Duplicate rows?
 

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?

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

--
Regards,
Tom Ogilvy


"85225" wrote in message
...
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?

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 InsertModule and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

Thanks! It worked!

"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

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

"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

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,
--
AP

"Lost in Alabama" a écrit dans le
message de ...
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

"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the
module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

I need to check from A:R.

Thanks,

Lost In ALabama

"Ardus Petus" wrote:

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,
--
AP

"Lost in Alabama" a écrit dans le
message de ...
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

"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the
module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

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

"Lost in Alabama" a écrit dans le
message de ...
I need to check from A:R.

Thanks,

Lost In ALabama

"Ardus Petus" wrote:

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,
--
AP

"Lost in Alabama" a écrit dans
le
message de ...
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

"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the
module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

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" wrote:

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

"Lost in Alabama" a écrit dans le
message de ...
I need to check from A:R.

Thanks,

Lost In ALabama

"Ardus Petus" wrote:

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,
--
AP

"Lost in Alabama" a écrit dans
le
message de ...
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

"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the
module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

I Tested it more thoroughly, everything seems OK

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

Yours,
--
AP

"Lost in Alabama" a écrit dans le
message de ...
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

How do I remove Duplicate rows?
 

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



"Ardus Petus" wrote:

I Tested it more thoroughly, everything seems OK

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

Yours,
--
AP

"Lost in Alabama" a écrit dans le
message de ...
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

How do I remove Duplicate rows?
 

"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

Hi Ken

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



Ken Hudson Wrote:
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 InsertModule and copy and paste the code from below into the
module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?






--
KH_GS
------------------------------------------------------------------------
KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
View this thread: http://www.excelforum.com/showthread...hreadid=400141

How do I remove Duplicate rows?
 

Terry,

Thank you so much for sharing this information.

Lost

"Terry Pfeifer" wrote:



"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

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


"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?

How do I remove Duplicate rows?
 

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






"Ken Hudson" wrote:

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 InsertModule and copy and paste the code from below into the module.
Close the visual basic editor.
Back at your workbook go to ToolsMacroMacros... 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

--
Ken Hudson


"85225" wrote:

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?