Deletion and flagging macro based on duplicates per row in multiple columns.

[DaGimp]

I need a macro that Can be run as a button from a different sheet in the same spread sheet. The sheet is called "RawData"

Criteria for Deletion.
per row (columns A and C match) AND (D is not a unique value).
Criteria for Flagging after deletions
Rows A and C match.

So basically I'm looking to eliminate a lot of noise from this spread sheet.
1. I want to keep any row that A and C dont match
2. I want to keep ONE row of any record that A and C do match per day.

Notes.
1. The data put into this sheet of the spread sheet will be manually updated from time to time. Number of rows up to 1.5 million (currently 350K).. After deletions it will average %15 of original number of rows.
2. This will be run on Excel 2013.
3. Data is pulled from this sheet in other sheets to create Charts.
4. Column J and K are Formulas pulling from E.
5. input Data will be sorted by Column C (A-Z) and secondary by Column E (date/time) newest to oldest.

User	Event	AD_SamAccountName	Result	Time Detected	Year	Month	Day	Time	Day OF Week	Date
user1	User account unlocked	user1	Success	6/27/14 9:55 AM	2014	6	27	9:55 AM	6	6/27/2014
user1	User account locked	user1	Success	6/26/14 1:34 PM	2014	6	26	1:34 PM	5	6/26/2014
user2	User account unlocked	user3	Success	4/24/14 11:25 AM	2014	4	24	11:25 AM	5	4/24/2014
user3	User account locked	user3	Success	4/24/14 11:13 AM	2014	4	24	11:13 AM	5	4/24/2014
user4	User account unlocked	user4	Success	6/7/14 9:48 AM	2014	6	7	9:48 AM	7	6/7/2014
user4	User account unlocked	user4	Success	6/7/14 9:48 AM	2014	6	7	9:48 AM	7	6/7/2014
user4	User account locked	user4	Success	6/3/14 5:37 PM	2014	6	3	5:37 PM	3	6/3/2014

<tbody>
</tbody>

 

[JLGWhiz]

See if this works on a copy first.

Sub cleanup()
Dim sh As Worksheet, lr As Long
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
            If .Cells(i, 1).Value = .Cells(i, 3).Value And _
            Application.CountIf(.Range("D:D"), .Cells(i, 4).Value) > 1 Then
                Rows(i).Delete
            End If
        End With
    Next
End Sub

 

[JLGWhiz]

You can substitute the actual sheet name for ActiveSheet. eg. Sheets("Sheet1"). If you are using an Active-X button with Click event the code would need to be in the Sheet code module of the same sheet the button is on. If you are using a Forms Control button that requires attaching the macro to it, then the code should be in the standard code module1.

 

[DaGimp]

[COLOR=#333333]Sub cleanup()[/COLOR]
Dim sh As Worksheet, lr As LongSet sh = ActiveSheet # now Sheet3lr = sh.Cells(Rows.Count, 1).End(xlUp).Row#loop for analysing row    For i = lr To 3 Step -1        With sh            If .Cells(i, 1).Value = .Cells(i, 3).Value And _            Application.CountIf(.Range("D:D"), .Cells(i, 4).Value) > 1 Then# it appears to be analizing column D? The date value is in Column K?                Rows(i).Delete            End If        End With    Next [COLOR=#333333]End Sub[/COLOR]

I've changed it to sheet 3 with no results.
I took some classes on programming in college but havent done it since. Can you comment out the script so I can try to find out what is wrong? I'd like to have this finished today.

It appears to evaluating the entire D range? The Data will be presorted before entering the worksheet. It will first be sorted on C and sub sorted on E.

With the presorting the logic could be something like this If Current Row = CR
(CR A = CR C) & (CR K = (CR +1)K) Then delete.