Help getting the interval of dates corresponding to one "yes" / "no" cells

[ropbasuel]

Dear all,

I have one excel file with thousands of rows composed of a date and a "yes" / "no" column, just like this:

Please Login or Register  to view this content.

I need help on a code that can search for where I have "YES" cells and then write on column C the intervals where I have YES, for example on this case would be from 01/01/2020 to 03/01/2020

I attach an example file of what my spreadsheet looks like and an expected result of the code.

Thanks so much for any help!

Regards

[modytrane]

Without VBA, just using formulas you can get results as shown in attached.
formula in column D [helper column. you can hide it]

Formula in column C [where you want the results]

This is an array formula. You need to enter this with ctrl+shft+ent.

see attached file.

[ropbasuel]

Dear modytrane,

First of all my apologies for the late reply. Had some internet connectivity issues.
Second, thank you so much for your time and for your code that works, although with some issues:

- I have a lot of spreadsheets with more than 100,000 lines each. The computer time calculation for each spreadsheet it is huge (sometimes more than 20min each). But more problematic than that, sometimes Excel crashes saying that I do not have sufficient resources to perform the calculation and then it simply crashes.

My question is if there is any code tweak or any optimization to perform this task?

Again thank you so much for your time!
Regards

[ropbasuel]

Can someone give me help on this please?

I'm kind of lost right now!

[nigelog]

Please Login or Register  to view this content.

[ropbasuel]

Dear nigelog,

Thank you so much for your help!! This code works perfectly!!! Can't thank you enough!!!!
I will give you reputation that you deserve!

Thank you!!

[nigelog]

Glad it worked out for you. Thankyou for the rep points and the kind feedback