Continuity serial number - Yes or No

[Myusrename001]

Team,

Based on ref number & TX number - i wanted to determine if the values are continued or not.

please guide me.

Thanks in advance... 

TX Number	Ref number	Result
0385	957192	-
0385	957196	No
0385	957198	No
0385	957203	No
0386	957187	-
0386	957191	No
0386	957194	Yes
0386	957195	Yes
0386	957196	Yes

[Luke_C · Accepted solution]

Hi @Myusrename001 

You'll want to use a multi-row formula:

1. Group by TX Number
2. Set rows that don't exist to NULL
3. For first row of TX number set to '-' (if previous row is NULL)
4. Otherwise check if the ref number is +1 from the previous row

IF isnull([Row-1:Ref number])
then '-'
elseif [Ref number] = [Row-1:Ref number]+1 
then 'Yes' 
else 'No' 
endif

[Myusrename001]

Thanks so much ! @Luke_C 

[flying008]

Hi, @Myusrename001 

just use Multi-Row Formula for you : (need set the option [Values for Rows that don't Exist] to 'Null' )