Consecutive entries using multi-row tool (reverse order)

[mtqromain]

Good morning everyone, 

I am trying to group values in a dataset when consecutive entries meet the same conditions. The problem is that my data is organized by date from newest to oldest, and I can't really change this because of other formulas. This means that I cannot use the MultiRow tool to reference the same column with the row below (row +1 on desired output column), as it seems to only work with row -1.

To be more precise, I attached an example of what I am trying to do below from my excel file: for one particular product, I flagged the start of a "collaboration" period and the end of this collaboration period in columns Q & R with "Yes". What I would like to get is the columns T & U on the right: returning the same value (i.e. the start date and end date) for all entries across a collaboration period, meaning a period where there have been collaboration events consecutively.

Alternatively, I would also like to know if it is possible to get the column S, meaning the number of consecutive collab events in reverse order (as my data is organized from newest to oldest.

Let me know if there is a smart way to do this, that does not involve changing the order of the dates. Thanks a lot for your help!

Best regards,

Romain

[mtqromain]

Maximum can be any value, sometimes i can have 10,15 or more consecutive values. Is there any way to account for that?

[ShankerV · Accepted solution]

Hi @mtqromain 

Got it, so we can expect as many values of 10 to 15 etc.

I have amended the workflow to work in such a way it will dynamically work for huge values also.

I have sample checked with 6 values and the output is as expected. Could you please test the same with your huge dataset too.

I have modified only this area.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[ShankerV]

Hi @mtqromain 

Please check and let me know if the amendments made in the worked for your huge dataset of more than 10 or 15 etc.

Thanks in advance.

[mtqromain]

thanks! this works great :) finally my model is running the way i want it to