Software

Ask something
Discussion Locked

Excel macro to compare & extract items

0 helpful
5 replies

by tracyp

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a list of thousands of names, account #s, addresses and another field denoting the type of address. basically what it looks like is this:

123 John Doe Main St. 00
456 Jane Smith Elm St. 00
456 Jane Smith Elm St. 30
789 Jim James Oak St 30
901 Mary Brown First St 00

What I want to do is extract all the accounts but when it runs into a duplicate account, only extract the one with a 30 and leave the other. In this list of thousands there may only be a couple dozen duplicates but since this list is refreshed and overwritten everyday by an external application I can't delete the duplicates once leaving the one I want to keep. In the above example I want to end up with 4 records.

Being brain dead at this point in the day it just occured to me that Access would do better for this problem. What do you think? The problem is everyone at work has Excel and only a few have Access. Once I get this working I want to hand it off to someone.

5 replies

by dklippert

Go to Data>AutoFilter to start with.
To get more exotic, check the Help file for how to set up and Advanced filter. The Advanced filter is very much like an Access Query

by abarnes70

Try this. Before you run the macro:

- sort your data by Acc # and then Address Type (ie 00 or 30), in ascending order.
- figure out the top and bottom row of the data.

Also, save copies of the file before testing!!!

Sub mcrRemoveDupes()
Dim intCol As Integer
Dim intCount As Integer
Dim intMaxRow As Integer
Dim intReply As Integer
Dim intRow As Integer
Dim intStartRow As Integer
Dim strTemp As String
Dim strTest1 As String
Dim strTest2 As String

On Error GoTo errHandler
intCount = 0
intCol = 1
intReply = MsgBox("Is the data to be cleaned in Column A and sorted?", vbYesNoCancel + vbDefaultButton1 + vbQuestion, "Data Location")
If intReply = vbCancel Then
Exit Sub
End If
If intReply = vbNo Then
intReply = MsgBox("Ensure the data to be cleaned is in Column A, then sort the data in Ascending order. When you're finished, run this macro again.", vbDefaultButton1 + vbInformation + vbOKOnly, "Data Location")
Exit Sub
End If
intStartRow = CInt(InputBox("Enter the starting row:"))
If intStartRow < 1 Then
Exit Sub
End If
intMaxRow = CInt(InputBox("Enter the bottom row:"))
If intMaxRow < 1 Then
Exit Sub
End If
For intRow = intMaxRow To intStartRow + 1 Step -1
Range(Cells(intRow, intCol), Cells(intRow, intCol)).Select
strTest1 = ActiveCell.Value
strTest2 = ActiveCell.Offset(-1, 0)
If UCase(strTest1) = UCase(strTest2) Then
Rows(intRow).Delete
intCount = intCount + 1
End If
Next intRow
intReply = MsgBox("There were " & intCount & " duplicate rows deleted.", vbInformation + vbOKOnly, "Complete")
Exit Sub
errHandler:
intReply = MsgBox("Enter a number only", vbOKOnly + vbCritical + vbDefaultButton1, "Parameter Error")

End Sub

by tracyp

Worked great. I did have to sort the address type as decending since the removing of duplicates deletes the second instance of the account number and I want the one that has 30 in it.

This discussion is closed to new replies.