• 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

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

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