Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplicates, sum column then delete duplicates
I need to decipher if a row is a duplicate based on matching data in cells I, K, L, N, and F. If the row is a duplicate, I need to add the value in cell F to the original row's value in cell F and then delete the duplicate row. The # of rows in the workbook changes everyday and the duplicates will not always be right below the row it is duplicating. Is this possible? Thanks for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplicates, sum column then delete duplicates
To clarify, there may be more than one duplicate row and they will always be directly below the original row they are duplicating. "aileen" wrote: I need to decipher if a row is a duplicate based on matching data in cells I, K, L, N, and F. If the row is a duplicate, I need to add the value in cell F to the original row's value in cell F and then delete the duplicate row. The # of rows in the workbook changes everyday and the duplicates will not always be right below the row it is duplicating. Is this possible? Thanks for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplicates, sum column then delete duplicates
Step #1) Make a copy of your file so you are not deleting records that you
don't intend to delete (you may not know what a macro does until you run it; it may do something that you don't expect it to do...) Step #2) Run one of these two macros (below): Sub CheckForDupes() Dim RowNdx As Long Dim ColNum As Integer ColNum = Selection(1).Column 'set number to match the proper column For RowNdx = Selection(Selection.Cells.Count).Row To _ Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then Cells(RowNdx, ColNum).Delete Shift:=xlUp End If Next RowNdx End Sub Sub DeleteDuplicateRows() Dim lLastRow As Long Dim lLastCol As Long Dim i As Long Dim j As Long Dim k As Long lLastRow = ActiveSheet.UsedRange.Rows.Count - 1 lLastCol = ActiveSheet.UsedRange.Columns.Count - 1 For i = 0 To lLastRow - 1 For j = lLastRow To i + 1 Step -1 For k = 0 To lLastCol If ActiveSheet.Range("A1").Offset(i, k).Value < ActiveSheet.Range("A1").Offset(j, k).Value Then Exit For End If Next k If k lLastCol Then ActiveSheet.Range("A1").Offset(j, 0).EntireRow.Delete End If Next j Next i End Sub Regards, Ryan--- -- RyGuy "aileen" wrote: To clarify, there may be more than one duplicate row and they will always be directly below the original row they are duplicating. "aileen" wrote: I need to decipher if a row is a duplicate based on matching data in cells I, K, L, N, and F. If the row is a duplicate, I need to add the value in cell F to the original row's value in cell F and then delete the duplicate row. The # of rows in the workbook changes everyday and the duplicates will not always be right below the row it is duplicating. Is this possible? Thanks for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplicates, sum column then delete duplicates
This isn't working quite the way I need. Here is an example of what I am
trying to accomplish: The following is an example of the set of data I'm starting with: Column C D E F G H 5 1 2 3 4 10 1 2 3 4 15 1 2 3 4 20 1 2 3 4 -6 2 3 4 5 -3 3 4 5 6 -4 3 4 5 6 25 1 2 5 7 And the following is what I want the data to do when I run the macro. Sum Column C and put the total in H when there are duplicates and then delete the extra rows of duplicates only. Column C D E F G H 5 1 2 3 4 50 -6 2 3 4 5 -3 3 4 5 6 -7 25 1 2 5 7 I hope this helps you to better see what I am attempting to do. Thanks for responding and please let me know if you have any more ideas. "ryguy7272" wrote: Step #1) Make a copy of your file so you are not deleting records that you don't intend to delete (you may not know what a macro does until you run it; it may do something that you don't expect it to do...) Step #2) Run one of these two macros (below): Sub CheckForDupes() Dim RowNdx As Long Dim ColNum As Integer ColNum = Selection(1).Column 'set number to match the proper column For RowNdx = Selection(Selection.Cells.Count).Row To _ Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then Cells(RowNdx, ColNum).Delete Shift:=xlUp End If Next RowNdx End Sub Sub DeleteDuplicateRows() Dim lLastRow As Long Dim lLastCol As Long Dim i As Long Dim j As Long Dim k As Long lLastRow = ActiveSheet.UsedRange.Rows.Count - 1 lLastCol = ActiveSheet.UsedRange.Columns.Count - 1 For i = 0 To lLastRow - 1 For j = lLastRow To i + 1 Step -1 For k = 0 To lLastCol If ActiveSheet.Range("A1").Offset(i, k).Value < ActiveSheet.Range("A1").Offset(j, k).Value Then Exit For End If Next k If k lLastCol Then ActiveSheet.Range("A1").Offset(j, 0).EntireRow.Delete End If Next j Next i End Sub Regards, Ryan--- -- RyGuy "aileen" wrote: To clarify, there may be more than one duplicate row and they will always be directly below the original row they are duplicating. "aileen" wrote: I need to decipher if a row is a duplicate based on matching data in cells I, K, L, N, and F. If the row is a duplicate, I need to add the value in cell F to the original row's value in cell F and then delete the duplicate row. The # of rows in the workbook changes everyday and the duplicates will not always be right below the row it is duplicating. Is this possible? Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Duplicates Across Two Sheets, Delete Everything else. | Excel Worksheet Functions | |||
How do I find (not delete) duplicates in multiple spreadsheets? | Excel Worksheet Functions | |||
Find duplicates, sum and delete dups | Excel Programming | |||
how do i find and delete duplicates in excel worksheet? | Excel Discussion (Misc queries) | |||
Find & Delete Duplicates across two Excel Worksheets | Excel Programming |