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

DaGimp

New Member
Joined
Jul 19, 2010
Messages
13
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.




UserEventAD_SamAccountNameResultTime DetectedYearMonthDayTimeDay OF WeekDate
user1User account unlockeduser1Success6/27/14 9:55 AM20146279:55 AM66/27/2014
user1User account lockeduser1Success6/26/14 1:34 PM20146261:34 PM56/26/2014
user2User account unlockeduser3Success4/24/14 11:25 AM201442411:25 AM54/24/2014
user3User account lockeduser3Success4/24/14 11:13 AM201442411:13 AM54/24/2014
user4User account unlockeduser4Success6/7/14 9:48 AM2014679:48 AM76/7/2014
user4User account unlockeduser4Success6/7/14 9:48 AM2014679:48 AM76/7/2014
user4User account lockeduser4Success6/3/14 5:37 PM2014635:37 PM36/3/2014

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,410
Office Version
  1. 2013
Platform
  1. Windows
See if this works on a copy first.
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,410
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

DaGimp

New Member
Joined
Jul 19, 2010
Messages
13
Code:
[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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top