Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 08, 09:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 78
Default 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   Report Post  
Old December 9th 08, 09:47 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 78
Default 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   Report Post  
Old December 11th 08, 07:19 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 2,836
Default 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   Report Post  
Old December 11th 08, 06:03 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 78
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Duplicates Across Two Sheets, Delete Everything else. waggett Excel Worksheet Functions 2 October 6th 09 02:01 PM
How do I find (not delete) duplicates in multiple spreadsheets? Nelson[_2_] Excel Worksheet Functions 1 June 1st 08 02:49 AM
Find duplicates, sum and delete dups John Excel Programming 0 November 15th 06 02:31 AM
how do i find and delete duplicates in excel worksheet? mrsthickness Excel Discussion (Misc queries) 2 February 28th 06 09:57 PM
Find & Delete Duplicates across two Excel Worksheets Lance[_4_] Excel Programming 12 October 14th 05 09:46 AM


All times are GMT +1. The time now is 12:43 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017