How to identify consecutive values?

[Jenis]

Hi,

I want to identify when the product column has 3 consecutive values or more. If the values is less than 3 consecutive values then the output should be false . I'm not sure how to get this type of output. Any help will be appreciated.

ID	Product	Max_Consec	Output
101	1	3	Consecutive
101	2	3	Consecutive
101	3	3	Consecutive
101	1	3	FALSE
101	2	3	FALSE
102	1	5	FALSE
102	1	5	FALSE
102	1	5	FALSE
102	1	5	Consecutive
102	2	5	Consecutive
102	3	5	Consecutive
102	4	5	Consecutive
102	5	5	Consecutive
103	1	4	Consecutive
103	2	4	Consecutive
103	3	4	Consecutive
103	4	4	Consecutive
103	1	4	Consecutive
103	2	4	Consecutive
103	3	4	Consecutive

[ShankerV]

Hi @Jenis 

You can do this with the help of multi row formula.

Many thanks

Shanker V

[damalraj22]

Hi, 

You will want to use the multi row formula to do this as Shanker said. Below I have a formula that I think should work. Let me know if you have any questions. 

IF [Row+1:ID]=ID and [Row+2:ID]=ID
THEN "Consecutive"
ELSE "False"
ENDIF

[TimN]

Hi,

Something like this?

[Jenis]

Hi @shankerV

Yes, I tried this but its not giving me that output.

If ([Product]-1 =[Row-1:Product] and [Product]+1 =[Row+1:Product]) Then “Consecutive” Else “False” Endif

[damalraj22]

Hi, 

Try using the formula below: 

IF ([Row+1:Product]=Product+1 and [Row+2:Product]=Product+2)
OR ([Row-1:Product]=Product-1 and [Row-2:Product]=Product-2)
THEN "Consecutive"
ELSE "False"
ENDIF

[Jenis]

Hi @damalraj22

Thanks for your response. When I applied your formula to my larger dataset, it worked exactly in most cases apart from this case. In ID-104, in the 6th row, Instead of giving consecutive, its giving false.

ID	Product	Max_Consec	Output
104	1	3	FALSE
104	1	3	FALSE
104	2	3	FALSE
104	1	3	FALSE
104	1	3	Consecutive
104	2	3	FALSE
104	3	3	Consecutive
104	1	3	FALSE
104	2	3	FALSE

[ShankerV]

Hi @Jenis 

Hope you are having a good day!!!

Please find the solution for you using the same Multi Row formula.

You can apply this for any huge set of data, will give you the expected output.

Have myself tried with some huge dataset, please find the result below.

Hope your issue is resolved. Feel free to mark helpful answers as a solution, so that future users with the same issue can find them easier!!!!

Many thanks

Shanker V

[ShankerV]

Hi  @Jenis 

Could you please check and confirm whether it worked.

If yes, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

[damalraj22]

Got it, you might need to add another statement in your formula command. Let me know if the formula below works.

IF ([Row+1:Product]=Product+1 and [Row+2:Product]=Product+2)
OR ([Row-1:Product]=Product-1 and [Row-2:Product]=Product-2)

OR ( ([Row+1:Product]=Product+1 and  ([Row-1:Product]=Product-1)
THEN "Consecutive"
ELSE "False"
ENDIF