• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing duplicates

M

Mohan122

New Member
Hi all,


please help to remove duplicates in excel by following step.


below is the table.

[pre]
Code: 
ID	Mobile		CSEUser		Date		ActionTime
384057	9591000002	depashreeshende	1-May-13	5/1/2013 7:17
384058	8870000003	rasidakhtar	1-May-13	5/1/2013 7:26
384059	8870000003	rasidakhtar	1-May-13	5/1/2013 7:27
384060	9920000009	rasidakhtar	1-May-13	5/1/2013 7:27
384061	9972000001	jaishreen	1-May-13	5/1/2013 7:27
384062	7870000006	rasidakhtar	1-May-13	5/1/2013 7:29
384063	7870000006	rasidakhtar	1-May-13	5/1/2013 7:30
[/pre]

in this duplicate should be remove by mobile,cseuser,date in that i have one condition

if same Cseuser same mobile and same date Actiontime less than 60sec is duplicate more that 60sec is not duplicate
 
Hi Mohan,


Is Helper Column is acceptable !!


Then, try with a Helper Column..

Use Formula as : =MROUND(E2,TIMEVALUE("0:0:60"))


Check the attached..


If helper is not acceptable then.. we can go for some other method.. With Index, Match & Countifs.. )


https://dl.dropboxusercontent.com/u/78831150/Excel/Removing%20duplicates%20%28Mohan122%29.xlsx


Regards,

Deb
 
if you don't mind using VBA you can use the below


it assumes the data (including headers) starts in A1

[pre]
Code: 
Sub Remove_Duplicates()

Dim Row_Count As Long
Dim i As Integer

Row_Count = Application.WorksheetFunction.CountA(Columns("A:A"))
If Row_Count < 3 Then
MsgBox "Not enough data to compare"
Exit Sub
Else
For i = 3 To Row_Count
If Cells(i, 2).Value = Cells(i - 1, 2).Value And _
Cells(i, 3).Value = Cells(i - 1, 3).Value And _
DateDiff("s", Cells(i, 5).Value, Cells(i - 1, 5).Value) <= "00:01:00" Then
Rows(i).Delete
i = i - 1
Row_Count = Row_Count - 1
End If
If i = Row_Count Then
MsgBox "finished"
Exit Sub
End If
Next i
End If
MsgBox "finished"
End Sub
[/pre]
 
@ dave,


Nice Code.. Just a lil suggestion.


In case of deletion.. always try to loop backward..


Code: 
For i = Row_Count to 3 step -1


and try to compare with complete row.. not only ROW - 1


Or use arrays.. to select all matching Non-Contigious ROWS at once.. then in last step delete all selected rows..


Regards,

Deb
 
@DaveTurton

Hi!

I haven't actually fully checked (yet, I'm gonna do it asap) VBA DateDiff function but if it's the implementation of the formula function DateDif it has a severe drawback. Perhaps you want to give a look to this topic, starting at this commment:

http://chandoo.org/forums/topic/excel-2007

Regards!
 
@DaveTurton

Hi!

I loaded my last uploaded file with a new G column and fill it using VBA DateDiff function. I know remembered that I have done this test yet (as a matter of fact twice) just to arrive at the same conclusion: VBA DateDiff function doesn't have the issues of old damned buried Excel function DateDif... so please discard my previous comment regarding possible effects in VBA code: DateDiff is healthy.

Regards!

PS: Uploaded file remains unchanged, as Led Zeppelin's song.
 
Hi all


Thanks for your reply but still i am not able to find the value..


duplicates should be remove by (same Cseuser same mobile and same date Actiontime less than 60sec) that means cseuser=mobile=1st actiontime-2nd actiontime less than 60sec are duplicate and >60sec are not duplicate... this very impt please help us..


Regards,

Mohan
 
@Mohan122

Hi!

Despite a contributor agrees to post his email I think it's advisable if you give a look at this link:

http://chandoo.org/forums/topic/locking-the-cell-automaticall-once-the-date-passed#post-21903

Regards!
 
You must log in or register to reply here.
Back
Top