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

[ShankerV]

Hi @mtqromain 

I got the essence of the issue. To understand it better, can you please share the input and the expected output separately.

Will be easy to figure out the best possible solution.

Many thanks

Shanker V

[ShankerV]

@mtqromain 

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.

FYI, multi row formula can be used for more than row-1 and row+1.

For that you need to increase the highlighted.

Please see the below.

Hope this helps!!!!

[mtqromain]

thanks for the reply!

Basically a simplified version of the inputs and outputs would look something like this (see excel file for reference):

input:

Ouput: 

thanks for your help!

[ShankerV]

 @mtqromain 

Got the necessary information, Thanks. 

[ShankerV · Accepted solution]

Hi @mtqromain 

Please find the below expected output.

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

[mtqromain]

Thank you very much for your help, this is great! especially the part with temp PEF and temp PSF, it will be very useful for other formulas in the future!!

Happy new year :)

[ShankerV]

Hi @mtqromain 

Welcome, glad my solution helped for your future too. Happy new year!!!

[mtqromain]

Actually, I am still facing a difficulty: it seems to work for small number of consecutive collaborations, but it does not work if it is above 3. Do you have any idea how I could fix it?

[ShankerV]

Hi @mtqromain 

I just built the solution based on the sample shared.

Could you please let me know how many consecutive collaboration is maximum.

Can work according to that and propose the solution/idea.

Many thanks

Shanker V