Compare and delete or return value?
 

Hello all, I'm new here and I hope somebody can help me out. I'm tryin
to figure out how to compare and delete data using a macro in excel.
have a workbook with 2 sheets. One sheet contains disconnected rat
info for accounts. The other sheet contains install info for accounts


Here is the problem. This is a commission report. We have an issue wit
our billing system that in order to add services to an account that ha
a campaign associated with it, our agents have to take services off o
the account to "break" the campaign. They complete the work order the
start a new one putting the existing services back on and then addin
the new services. Unfortunately, the query pulls all of that data an
it looks like the agent sold more services then they actually did. Th
result would be the agent getting paid more than they should.

What i'm trying to do is create a macro that will compare th
disconnect and install sheets. If a row in the install sheet matche
the row in the disconnect sheet, delete the row and move onto the nex
row. If I can't delete the row, perhaps I can return a value of true i
any rows in one sheet match the other.

Here is an example of my two sheets. Could someone lead me in the righ
direction? Any help would be awesome. Thanks

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55605
--
Message posted from http://www.ExcelForum.com

Compare and delete or return value?
 

shaun,


It sounds like you only want the new codes left
along with the existing codes on sheet 0 to 1. so if there is a code o
1 to 0 and on 0-1 you want to delet it from 0-1 ?
Mybe I'm still confused...

Charle

--
Message posted from http://www.ExcelForum.com

Compare and delete or return value?
 

What i want is to compare rows. I want to take row 1 from sheet 0 to
and compare it to a range in sheet 1 to 0. If agent id, account numbe
and rate match all on the same row, delete the row and move onto th
next one. Or return a match value so I can manually sort and delete
Here is a sample of the table.

http://planetshaun.net/shaun.xls

This is really my first crack at vba so any help you can give would b
extremely appreciated. Thanks

--
Message posted from http://www.ExcelForum.com

Compare and delete or return value?
 

shaun

Here is what I came up with. Copy it to a module. Be sure you test i
on a copy of your data. It should delete info on the 0 to 1 sheet.


Sub delete_dups()
Application.ScreenUpdating = False
Dim i As Long
Dim a As Long
Dim rng As Range
Dim rrng As Range
Dim agent, accnr, svscode
Dim agent1, accnr1, svscode1
Worksheets("From 0 to 1").Activate
Set rng = Worksheets("From 0 to 1").Cells(1, 1).CurrentRegion
For i = 2 To rng.Rows.Count
agent = rng(i, 2).Value
accnr = rng(i, 5).Value
svscode = rng(i, 14).Value
Worksheets("From 1 to 0").Activate
Set rrng = Worksheets("From 1 to 0").Cells(1, 1).CurrentRegion
For a = 2 To rrng.Rows.Count
agent1 = rrng(a, 2).Value
accnr1 = rrng(a, 5).Value
svscode1 = rrng(a, 14).Value
If agent1 = agent And accnr1 = accnr And svscode1 = svscod
Then
Worksheets("From 0 to 1").Activate
rng(i, 2).EntireRow.Delete
i = i - 1
Exit For
End If
Next a
Next i
End Sub


Hope this is what you are look for.

Charle

--
Message posted from http://www.ExcelForum.com