Delete Duplicate row and Copy to another sheet
 

Dear All:

I have following data, what I m trying to do, records where both HOC
and STS are same, irrespective of IDATE/SDATE/RL. it

should be deleted from sheet1 and be copied on sheet 2. Would it
possible in macro.


IDATE PNAME STS RL SDATE
13/08/2006 HOC AT Y 14/08/2006
13/08/2006 HOC AG Y 23/08/2006
13/08/2006 HOC AG H 24/08/2006
13/08/2006 HOC UP Y 27/08/2006
13/08/2006 HOC FILE H 30/08/2006
14/08/2006 IASS AT H 15/08/2006
14/08/2006 IASS AG H 19/08/2006
14/08/2006 IASS UP H 22/08/2006
14/08/2006 IASS WDT H 23/08/2006
14/08/2006 IASS FILE H 24/08/2006
14/08/2006 IASS FILE H 27/08/2006

After impelmention the data should be like this


IDATE PNAME STS RL SDATE
13/08/2006 HOC AT Y 14/08/2006
13/08/2006 HOC AG Y 23/08/2006
13/08/2006 HOC UP Y 27/08/2006
13/08/2006 HOC FILE H 30/08/2006
14/08/2006 IASS AT H 15/08/2006
14/08/2006 IASS AG H 19/08/2006
14/08/2006 IASS UP H 22/08/2006
14/08/2006 IASS WDT H 23/08/2006
14/08/2006 IASS FILE H 24/08/2006

Delete Duplicate row and Copy to another sheet
 

Something like this
Sub CopyDups()
Dim lastrow as Long, rw as Long
rw = 2
with worksheets("Sheet1")
lastrow = .cells(rows.count,2).end(xlup).row
for i = lastrow to 2 step -1
if .cells(i,2) = .cells(i-1,2) and _
.cells(i,3) - .cells(i-1,3) then
.rows(i).copy Worksheets("Sheet2").Cells(rw,1)
.rows(i).Delete
rw = rw + 1
end if
Next
End With
End Sub

--
Regards,
Tom Ogilvy


"-jawad" wrote:

Dear All:

I have following data, what I m trying to do, records where both HOC
and STS are same, irrespective of IDATE/SDATE/RL. it

should be deleted from sheet1 and be copied on sheet 2. Would it
possible in macro.


IDATE PNAME STS RL SDATE
13/08/2006 HOC AT Y 14/08/2006
13/08/2006 HOC AG Y 23/08/2006
13/08/2006 HOC AG H 24/08/2006
13/08/2006 HOC UP Y 27/08/2006
13/08/2006 HOC FILE H 30/08/2006
14/08/2006 IASS AT H 15/08/2006
14/08/2006 IASS AG H 19/08/2006
14/08/2006 IASS UP H 22/08/2006
14/08/2006 IASS WDT H 23/08/2006
14/08/2006 IASS FILE H 24/08/2006
14/08/2006 IASS FILE H 27/08/2006

After impelmention the data should be like this


IDATE PNAME STS RL SDATE
13/08/2006 HOC AT Y 14/08/2006
13/08/2006 HOC AG Y 23/08/2006
13/08/2006 HOC UP Y 27/08/2006
13/08/2006 HOC FILE H 30/08/2006
14/08/2006 IASS AT H 15/08/2006
14/08/2006 IASS AG H 19/08/2006
14/08/2006 IASS UP H 22/08/2006
14/08/2006 IASS WDT H 23/08/2006
14/08/2006 IASS FILE H 24/08/2006