Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I automatically remove duplicate data within a column? | Excel Discussion (Misc queries) | |||
Adding Rows to Master Sheet | New Users to Excel | |||
Preventing Duplicate Entries in rows | Excel Worksheet Functions | |||
Remove empty rows in excel? | Excel Discussion (Misc queries) | |||
How do I remove blank rows in Excel? | Excel Discussion (Misc queries) |