Compare and delete or return value?

S

shaun nieves

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
 
S

shaun nieves

:confused: Does anyone want to take a crack at this or perhaps jus
crack me in the head?? ;
 
C

Charles

shaun,


What fields are you compairing. your example on sheets("both") show
all agents with none deleted.

Let me know and I'll look at it.


Charle
 
S

shaun nieves

I'm trying to compare sheet From 0 to 1 and sheet From 1 to 0.

Ideally, what I want to do is write a macro that will compare the tw
sheets, delete the duplicate entries and leave what's left on the Fro
0 to 1 sheet.

In the example, sheet From 1 to 0 shows the agent removed

digconv, digtier, expd, hbomax and pldigpk.

sheet From 0 to 1 shows she added

digconv, expd, hbomax, plbgrfh and tierfam.

What I need to do is delete the rows that contain digconv, expd, an
hbomax leaving plbgrf and tierfam as those are the only two rates the
added.

I know I need to delete some columns so that I only have sales number
account, date and svc code. If I use this criteria to compare, i
should delete them right?

Thank you for your help!!!! :
 
C

Charles

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
 
S

shaun nieves

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
 
C

Charles

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
 
Top