Remove dups based on time >30 min

V

vondo21

New Member
Joined
Feb 3, 2007
Messages
4
Hi, first time caller, long time viewer.

I have a sheet that contains:
CSR_ID UPDATE_USERID EFFDT
243 KHANADEL 4/25/08 7:45 AM
243 LINLAN 4/25/08 8:56 AM
243 LINLAN 4/25/08 8:57 AM
243 LINLAN 4/25/08 8:57 AM
243 LINLAN 4/25/08 8:57 AM
17791 KHANADEL 4/1/08 7:34 AM
17791 KHANADEL 4/1/08 7:38 AM
17765 LINLAN 4/1/08 8:24 AM
17765 LINLAN 4/1/08 8:29 AM

I need to remove the dups based on time stamp. My expected results would look like:

CSR_ID UPDATE_USERID EFFDT
243 KHANADEL 4/25/08 7:45 AM
243 LINLAN 4/25/08 8:57 AM
17791 KHANADEL 4/1/08 7:38 AM
17765 LINLAN 4/1/08 8:29 AM

As you can see, records were removed based on time > 30 minutes BUT I still have duplicates. My staff works on requests; I just need to remove the duplicates that occur within minutes of each other, as that represents the same effort - were 30 minutes later represents new effort by my staff.

Thanks to anyone who has suggestions. - This place rock!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Welcome to the board.

You could do this with a helper column to identify duplicates, and then filter and delete all duplicate rows:

Excel Workbook
ABCD
1CSR_IDUPDATE_USERIDEFFDTDups
2243KHANADEL25/04/08 07:450
3243LINLAN25/04/08 08:563
4243LINLAN25/04/08 08:572
5243LINLAN25/04/08 08:571
6243LINLAN25/04/08 08:570
717791KHANADEL01/04/08 07:341
817791KHANADEL01/04/08 07:380
917765LINLAN01/04/08 08:241
1017765LINLAN01/04/08 08:290
A


With this table, you would filter on the duplicates column for >0 and delete visible rows.

This method assumes that duplicates are sorted in time/date order with the latest time last (per your list above). I did this to deal with multiple results with the same date and time. I'm sure that a more robust method is possible if that is required though.
 
Upvote 0
Thanks cornflakegirl for the response - I did try the formula array but it produced the below outcome which I need to then manualy filter for 0's and whatelse is >30 mins, as the numbers are not consistant. For example, I might only have 0 and 1, or 0 and 1,2,3 and the 3 is the >30 minutes. I would be filtering all day.

What would be ideal is a macro that would look at the CSR ID, store it a array, then delete and duplicates that are <30 mins apart (My coding is out of date)... Also, the other issue with the formula is the drain on the CPU when Calculate is performed, as I have on average 2400 rows.

CSR_ID UPDATE_USERID EFFDT DUP
239 PHOANG 4/11/08 10:27 AM 5
239 PHOANG 4/11/08 10:27 AM 4
239 PHOANG 4/11/08 10:34 AM 3
239 PHOANG 4/11/08 10:54 AM 2
239 PHOANG 4/11/08 10:54 AM 1
239 PHOANG 4/11/08 10:55 AM 0

cornflakegirl - your awesome for the formula, I will tuck it away for small lists.

Thanks!
 
Upvote 0
I'm not sure I understand - just do a custom filter for >0, and delete the visible rows - you get rid of 1-5 in one go.

You could record a macro of putting in the formula, filtering, deleting rows, then removing the dup column, if you want to automate. You could write some code to do the comparisons, but I think it would have to loop line by line (can't think of another way) so would be relatively inefficient.
 
Upvote 0
Thanks Emma,
The code is great and I tweaked it, but it kills the desktop CPU/memory usage every time I calc it.

Thanks again for your time on this.
 
Upvote 0
Okay - this is how I would do it with looping. (It's probably possible to do it more efficiently with a dictionary or something, but you need someone cleverer for that!)

Code: 
Sub DeleteDups()
Dim lLastRow As Long, lCounter As Long

Application.ScreenUpdating = False

lLastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:C" & lLastRow).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range( _
        "B1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

For lCounter = lLastRow To 3 Step -1
    If Range("A" & lCounter).Value = Range("A" & lCounter - 1).Value Then
        If Range("B" & lCounter).Value = Range("B" & lCounter - 1).Value Then
            If Range("C" & lCounter).Value < Range("C" & lCounter - 1).Value + 1 / 24 / 2 Then
                Rows(lCounter - 1).Delete
            End If
        End If
    End If
Next lCounter

Application.ScreenUpdating = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
A
  • Question
GET MAX TIME VALUES BETWEEN TWO RANGES IN EXCEL LIST
2
Replies
19
Views
743
AFZAL SOHAIL
A
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
259
mst3kr
M
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
342
awoohaw
awoohaw
P
  • Question
workday, but even more flexible
Replies
6
Views
379
KRice
K

Forum statistics

Threads
1,215,995
Messages
6,128,180
Members
449,430
Latest member
sadielynn7

Share this page

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
Back
Top