Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy duplicate rows to new sheet in WB | Excel Programming | |||
How do i delete duplicate rows in an Excel Spread Sheet | Excel Worksheet Functions | |||
DELETE DUPLICATE DATA IN A SHEET AUTOMATICALLY | Excel Discussion (Misc queries) | |||
Macro to copy duplicate rows (not delete) | Excel Programming | |||
Macro - to copy duplicate rows to another sheet | Excel Worksheet Functions |