Look for duplicates to Eliminate row

Microsoft Office excel 2007 plain & simp...
April 2, 2010 at 14:00:50
Specs: Windows Vista
I have a spreadsheet that lists all the this business' outstanding bills, the bills are placed into the spreadsheet as they come in, What I'm trying to do is set up a Macro to eliminate the Duplicates, however I want the oldest due date and the newest amount due to stay. All data starts in row13, "D &E" contain the vendor and Account number so that is the location that I will be looking to duplicates, I want to delete the previous entires, which "A" has the date in which the bill was received. however I want the Due date in column "G" to remain the oldest due. The rest of the information needs to be the most current. I have already received help from you with a Macro to move the Paid bills off this page and it worked GREAT!!!!, Thanks. The data runs from column "A - M"

See More: Look for duplicates to Eliminate row

Report •

#1
April 2, 2010 at 14:42:03
re: the oldest due date

Just to be sure there is no confusion, what is your definition of the "oldest due date"?

Assuming today is April 2, 2010, please provide 2 due dates and tell us which one is the "oldest".


re: I want the oldest due date and the newest amount due to stay

Is this one item that stays or two?


re: "D & E" contain the vendor and Account number so that is the location that I will be looking to duplicates

Does the code need to look at both to find duplicates or either one?


re: eliminate the Duplicates

Will there be more than 2 entries that could match? In other words does the code have to find the "oldest due date" between just 2 duplicates or between 3, 4, 5, etc. duplicates.


Report •

#2
April 2, 2010 at 15:18:09
due dates 3/1/10, 2/1/10.....2/1/10 would be the oldest


oldest date to stay the new amount would be included in the most current row added.


Both, due to the fact that are times that account numbers maybe the same even though the vendor is different

Should only be two, as when I add a second one I would like to eliminate before I would add another.


Report •

#3
April 2, 2010 at 16:57:56
So, if I understand this correctly...

Today is 4/2/2010

Your sheet already has:

XYZ 123 3/1/2010 $400

and

XYZ 123 2/1/2010 $600

Today you entered:

XYZ 123 4/2/2010 $800

What you should have left is:

XYZ 123 2/1/2010 $600

and

XYZ 123 4/2/2010 $800

Is that right?


Report •

Related Solutions

#4
April 3, 2010 at 04:10:54
sorry for the confusion....if I have

XYZ 123 3/1/10 300.00

and I add

XYZ 123 4/1/10 400.00

then after the Code I want to end up with

XYZ 123 3/1/10 400.00

and I only want one, I want to eliminate any Duplicates.

Thanks


Report •

#5
April 4, 2010 at 01:12:44
So, bottom line is this:

1 - When you add a new row of data, there may be one - and only one - other row above it that contains a matching Vendor in D and Account Number in E.
2 - The new row should display the date from the "original" row (Column G)
4 - The original row should be deleted

You didn't mention what column the amount is in, so I'm going to use H. Modify the following code as needed.

I'm also assuming that there are currently no duplicates in your list.

Make sure you run this code in a backup copy of your workbook in case something goes terribly wrong.

As written, the code needs to be run manually, but it could be placed in a Worksheet_Change Event so that it runs right after you enter data in a specific column.

Option Explicit
Sub PaymentDue()
Dim lastRow As Integer
Dim firstAddress As String
Dim c
'find last Row in Vendor list
 lastRow = Range("D" & Rows.Count).End(xlUp).Row
'Search for Vendor in list above the new entry
  With Range("D13:D" & lastRow - 1)
   Set c = .Find(Range("D" & lastRow), lookat:=xlWhole)
'If found then...
    If Not c Is Nothing Then
     firstAddress = c.Address
      Do
'...Check Account Number
       If Range("E" & c.Row) = Range("E" & lastRow) Then
'If it matches, Copy old Due Date, Delete old Row and Exit
         Range("G" & lastRow) = Range("G" & c.Row)
         c.EntireRow.Delete
         Exit Sub
       End If
'If Account Number doesn't macth, Search for Vendor again
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
End Sub



Report •

#6
April 17, 2010 at 06:46:54
The code seems to be working perfectly except for the fact it seems like if I have more than 1 match at a time than it will only pick one to eliminate and if I try to update it again it still leaves the match there. example given would be if we have.....

abc 123 300.00 4/18/10
def 456 500.00 4/18/10

and I add the following to the list.....

abc 123 600.00 5/18/10
def 456 1000.00 5/18/10

afterwards the list would look like

abc 123 300.00 4/18/10
abc 123 600.00 5/18/10
def 456 1000.00 4/18/10

not sure how else to explain it hope this helps, thanks


Report •

#7
April 17, 2010 at 07:26:03
That's because the code is designed to be run after each new entry.

Since it only checks the last entry in Column D ("def" in your latest example) against everything above it, it essentially ignores the new "abc".

You have 2 choices:

1 - Run the code manually after each new set of data is complete.

2 - Set it up as a Worksheet_Change so that it runs automatically after each change.

The only issue with Option 2 is that you have to decide when you want it to run. i.e. After Column D is changed? After Column E is changed? After every change? (more flexible, but less efficient) After Column E is changed but only if there is data in Column D and/or vice versa? etc.

If you choose to go with a Worksheet_Change macro, there will need to be some modifications to the code and you'll need to be very specific about when you want it to run.


Report •

#8
April 20, 2010 at 12:49:38
whatever is going to be easiest, I really don't want to run it after every entry, columns "A - I" have data in them, is there anyway that it can be set up to run everytime data is input into another row into those columns...not sure i worded that right. If you tell me how it is set up I will learn to use it, I just really don't want to have to manually run it everytime. thanks

Report •

#9
April 20, 2010 at 14:08:11
re: "is there anyway that it can be set up to run everytime data is input into another row into those columns..."

I put the code inside a Worksheet_Change event, set to trigger on a change to Column I.

What this means is that you can make any changes you want to in your spreadsheet and nothing will happen until you make a change to a cell in Column I.

Right after you make the change in Column I, the event will fire and the code will do exactly what it was doing before - i.e. take the data in the last row and update the sheet.

re: "If you tell me how it is set up I will learn to use it"

Enter your data in A - H first, and then enter your data in I. Once the data is entered in I, the code will run.

Note: Any change in Column I, anywhere on the sheet, will cause the code to run. If there are no duplicates, nothing should happen, so that may not be a big deal, I just wanted you to be aware of that fact.

If you want the code to fire on a change to a different column, change the 9 to be the number of the column you want to trigger the code with.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Integer
Dim firstAddress As String
Dim c
'Run code after entry is made in Column I
 If Target.Column = 9 Then
'find last Row in Vendor list
  lastRow = Range("D" & Rows.Count).End(xlUp).Row
'Search for Vendor in list above the new entry
   With Range("D13:D" & lastRow - 1)
    Set c = .Find(Range("D" & lastRow), lookat:=xlWhole)
'If found then...
     If Not c Is Nothing Then
      firstAddress = c.Address
       Do
'...Check Account Number
        If Range("E" & c.Row) = Range("E" & lastRow) Then
'If it matches, Copy old Due Date, Delete old Row and Exit
          Range("G" & lastRow) = Range("G" & c.Row)
          c.EntireRow.Delete
          Exit Sub
        End If
'If Account Number doesn't macth, Search for Vendor again
         Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
     End If
   End With
 End If
End Sub


Report •

Ask Question