Multi Row formula to search like a loop

Question

I've created a multi row formula based on some conditions and it goes for searching 10 rows below since it would be pointless  to add more but I found that it can happen, so my question is: is there some better/faster solution for my case.

These are the rules for searching:

1. First task can start with any "error_bucket" (FFF, SSS, Other)

2. Next step is to find the same "error_code" (first below) and it can start only with "FFF" or "SSS" and when it finds it, calculate "open_time" - "closed_time". For my testing I just hit the flag 0/1 to see if the formula works fine.

Attached is a solution that I created but as I said, I found that some cases can be within 15 or 20 rows that I have not covered with my "10 Num Rows" within the Multi Row formula.

Is it possible to create some kind of loop that will search my conditions within the same "customer_no", "country" and "model_nbr" based on the rules for the "error_code" and "error_bucket".

Thank you in advance.

Multi row with conditions.yxmd

cito · Answer

@danilang

Thank you for the feedback for flow. I have reuploaded it.

Regarding conditions, I just took small data sample from my table and the key point here is the next:

- I start from the first record, based on "customer_no", "country" and "model_nbr" (in the example I have just one "customer_no") and this would be the line break point for multirow formula. When it finds new "customer_no", "country" and "model_nbr" start from the beginning.

For the conditions, to clarify once more:

1. I start from the first line (row 2 in the excel file, green one) - error_code = 744 and error_bucket=FFF.

2. For the first line, now, I need to find a record (just the first next) that has the same "error_code" but the "error_bucket" can be FFF or SSS (can't be "Other").

3. So, for the first line,  error_code = 744 and error_bucket=FFF, the first match I find in row 7 (green one), since it has the "error_code" = 744 and the "error_bucket" = SSS.

4. Next, I go to the second line (row 3 in the excel file) - error_code = 75 and error_bucket=SSS.

Since I do not have the "error_code" = 75 below this one, I will flag this line(record) as 0.

5. First record can start with any error_bucket = "Other" or "SSS" or "FFF" but the match one needs to be either "FFF" or "SSS" with the same error_code.

In other words, I just need to find the first one match, based on "error_code" and "error_bucket", so back to your key point (yellows highlighted) :

- Line 9 has error_code = 744 and error_bucket = Other.

- I need to find (only) the next first one line that has the same error_code = 744, but the error_bucket needs to be either "FFF" or "SSS".

- Line 9 has a match with line 12 and that's the end of my "chase".

- When I come to line 12, error_code = 744 and error_bucket = FFF, I need to see if I have, below it, a match and I have it in Line 13, and so on and so forth.

Did I manage to clarify the rules a bit :)

danilang · Answer

Hi @cito

Can you provide an exhaustive list of detailed rules as to how the matches are flagged(or not as the case may be).

Ambiguous examples

-you say that the two yellow lines match, but the only value they have in common is the error_code.  Why doesn't line 9(1st yellow) match line 16 as well?

-line 15(sand coloured) has the notation "doesn't have a match below".  but error code 777F appears in line 17?

BTW: The workflow that you attached doesn't have your multirow tool in it.

Dan