• 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.

Find duplicates with sumproduct

K

Karthik Thandapani

Member
Hi,

I have a list of users with From & To time ranges. I want to find duplicates if they have sameUser+fromTime+ToTime (also time range falls between too). Presently I have =IF(SUMPRODUCT(--($B$2:$B$11=$B3),--($C$2:$C$11<=$C3),--($D$2:$D$11>=$D3))>=1,"Duplicate","Not a duplicate") which is not finding the exact duplicates.

Basically, I need to find matching user names those who have similar time ranges. Can anyone please help. Please see the attached file.

Thanks,
Karthik
 

Attachments

  • Find duplicates.xlsx
    9.3 KB · Views: 5
Hi karthik,

Try in E2 and copy down:

=IF(SUMPRODUCT(($B$2:$B$11=B2)*($C$2:$C$11=C2)*($D$2:$D$11=D2))>1,"Duplicate","No Duplicate")

Regards,
 
Hi Somendra,

It works only for the exact user+time match. But I require for between their time ranges too. For an example: UserA is busy from 7:00 To 8:00. Again, UserA is busy from 7:10 to 7:30 but against that line, its not showing up as duplicate.

Thanks for trying.
 
Sure Somendra, Please see the attached file. From that, RBC is busy from 7:00 AM to 10:AM. Also in another record RBC is busy from 7:00 AM to 9:00 this should actually show as duplicate. Hope this helps
 

Attachments

  • Find duplicates.xlsx
    10.2 KB · Views: 6
Karthik Thandapani said:
Sure Somendra, Please see the attached file. From that, RBC is busy from 7:00 AM to 10:AM. Also in another record RBC is busy from 7:00 AM to 9:00 this should actually show as duplicate. Hope this helps
Click to expand...

This is not matching with the times in the file. Kindly Clarify.

Karthik Thandapani said:
@Somendra Misra , just a thought:
1. Can we find the first digit of the starting time and mark if duplicate?
2. Or find the first digit & add =time(,30,)
Click to expand...

Time in excel are number 0-1 where 1 day = 24 hours = 1, so 7:00 A.M. for excel becomes 7/24 for calculation purpose. The 7 that you see is the formatting applied so you can't extract the 7. What you can can do is extract the hour, minute & second from this number.

Regards,
 
Hello Karthik,
Try:
=SUMPRODUCT((UsersList=B2)*(FromTime<=C2)*(ToTime>=D2))

It will provide a count of overlapping times. Anything with a count greater than 1 would be a duplicate.

Cheers,
Sajan.
 
Hi @Somendra Misra , the formula you've provided finds only the exact match. For an example; say ABC 2:00 AM to 4:00 and ABC 2:10 AM to 4:00 AM are duplicates as their time overlaps. This is exactly what I want to capture.
Please see the attached file. The yellow highlighted ones should be duplicates but, can we tweek the existing formula a bit to derive that?
 
@Somendra Misra , sorry it's not satisfying the requirement. All I need is to find duplicates if times match and also overlaping times as explained in my previous comment.
Is that possible to find ABC 10:00 AM to 11:00 AM and 10:00 AM to 10:40 AM are duplicates?
 
You must log in or register to reply here.
Back
Top