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

identifying rows as duplicate sequences if the dates are overlapping

mithelesh

mithelesh

New Member
:DDHi all

Good Day!!

I am working on a project. where i need to display the overlapping dates as "Duplicate

Sequences" . I have attached raw data related to this. How we use "If condition or any other

formula to display the rows as dupilcate sequences.

Regards
Mithelesh.S
 

Attachments

Put this formula in H2, copy down as needed.
=IF(OR(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A1:A$2=A2)*(C2>D1:D$2)+(A1:A$2=A2)*(D2<C1:C$2))),"Good","Duplicate")
 
Hi Luke ,

Thanku for the reply this formula was very helpful.

It gave me an insight how these formulas will help.

Regards
Mithelesh.S
 
:)Hi Luke ,

Good Day!

Please suggest how to put this sumproduct formula for the data in the sheet provided.

I tried to do it but i didnot get the correct result.

"Expected result" column is the required intent. Thank you for the support. Please find the attachment.

Regards
Mithelesh.S
 

Attachments

Your "dates" in columns E:F have changed. Instead of being true dates, col E is now text strings, and F is just a number. Dates needs to be in proper format (e.g., mm/dd/yy), not just 1 big number like that.
 
Hi Luke :)

I have changed the date format. But i see only the last two rows coming up as duplicates.

But I need the result as mentioned in "EXPECTED RESULT" Column.

Please suggest .

Regards
Mithelesh.S
 

Attachments

Looks like your unique identifier Code is no longer in column C, but in col B. If you move the data, the formula would need to be changed.
Corrected formula:
=IF(OR(COUNTIF(B$2:B2,B2)=1,SUMPRODUCT((B1:B$2=B2)*(E2>F1:F$2)+(B1:B$2=B2)*(F2<E1:E$2))),"Good","Duplicate")
 
Hi Narayan/Luke:DD

any idea on formating the dates.


910101 should be 01/01/91 and 1391231 should be 12/31/2039
1120701should be 07/01/2012 1391231 shoudl be 12/31/2039 .

Regards
Mithelesh.S
 
Can you confirm your dates you gave? I'm not sure how 1391231 should be converted to 12/31/2039. The extra digit seems quite odd.
 
Hi Luke,

These dates are pulled from database. i dont know why the setup is like that .

but when we pull the query we get the dates in this format. (for eg: 1391231-- this is nothing but 12/31/2039)


Thanks and Regards
Mithelesh.S
 
Sounds like the database stores years starting at a literal 0, rather than 1900. Odd, but I can work with that.
=DATE(LEFT(A2,LEN(A2)-4)+1900,LEFT(RIGHT(A2,4),2),RIGHT(A2,2))

That formula will convert the number string to an actual date.
 
You must log in or register to reply here.
Back
Top