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.


9a0hg6.jpg


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>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
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,979
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,643
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top