matching and deleting

M

mrharpo2u

i have two work sheets in one spreadsheet. they are from different systems
one feed to the second to create refunds. I have been forced to cut and
paste both sheets to a new worksheet and then sort by amount and then delete
like amounts to find refunds not carried over to the other system. Is there
an easier way to match and then delete matching amounts, therefore saving me
valuable time.
 
J

JLatham

I could come up with a coding (Macro) solution. Would need to know if the
two values to be compared are in the same column (as in duplicate entries in
2 or more rows) or if there are 2 cells on a single row to be compared.

Are the entries to be compared on one worksheet or a group on one worksheet
to be compared with a group on a second worksheet. From your write up, it
looks like there's a point where everything is on a single worksheet when you
start looking for ones to delete.
 
M

mrharpo2u

Yes the two values to be compared are ont he same single row.once I merge the
two worksheets. ie. all values to have all duplicates deleted are all on row
E with their names being on Row B and A:
A B C D E
(Williams.... John...12 main st....45345......$250.00).System 1
Williams.....J..........12993.....06/22/06......250.00. System 2

I hope this helps you to better help me.
 
J

JLatham

I should be able to finish this up this evening. I wrote something up for
some else here at the site just a few days ago - same thing, check if two
values in 2 rows exactly match and delete one of them if they do.

Basically, if A1 = A2 and B1 = B2, then delete one of them (probably row 2).
Will also add 3rd check for the amounts, so if last name, first name and
amounts are all 3 the same, good to delete one of the entries:
if A1 = A2 and B1 = B2 and E1 = E2, then delete one of the rows.
 
J

JLatham

Here's code that will do what you want with a sorted list (probably want to
sort by last name, first name AND the amount in column E to make sure
duplicate entries end up in sequential rows.

Sub DeleteRows()
'deletes one of two duplicate rows
'duplicate is determined in this
'routine as Cells in Columns A, B and E
'being same value on 2 adjacent rows
'
'presumes you have the correct sheet
'selected when you call this routine
'
Dim LastRowUsed As Long
Dim RowOffset As Long
'find the last used row on the sheet
LastRowUsed = Selection.SpecialCells(xlCellTypeLastCell).Row

RowOffset = LastRowUsed
Do Until RowOffset = 2
If Range("A1").Offset(RowOffset, 0) = Range("A1").Offset(RowOffset -
1, 0) Then
If Range("B1").Offset(RowOffset, 0) =
Range("B1").Offset(RowOffset - 1, 0) Then
If Range("E1").Offset(RowOffset, 0) =
Range("E1").Offset(RowOffset - 1, 0) Then
Rows(RowOffset & ":" & RowOffset).EntireRow.Delete

End If
End If
End If
RowOffset = RowOffset - 1
Loop

End Sub

Starting with a group like this:
Helmann Groucho $21.40
Helmann Groucho $195.00
Johnson Chico $44.95
Johnson Chico $44.95
Johnson Chico $38.92
Johnson Chico $21.40
Zeppo Jim $150.00
Zeppo Jim $150.00
Zeppo Jim $150.00

you will end up with a list that looks like this:

Helmann Groucho $21.40
Helmann Groucho $195.00
Johnson Chico $44.95
Johnson Chico $38.92
Johnson Chico $21.40
Zeppo Jim $150.00
 
Top