Find and filter by adjacent values
May 20, 2021
Example
Part of the data in the file book1.xlsx is shown in the figure below:
It is required to extract data based on the Value2 column. Whenever the data changes from a negative value to 0, extract the row where the 0 is located.
Write SPL script:
A1: Read the data in the file book1.xlsx
A2: Select the record whose Value2 is equal to 0 and Value2 of the previous row is less than 0, Value2[-1] represents the Value2 value of the previous row.
The same is true: Value2[-2] represents the Value2 value of the upper 2 rows, and Value2[2] represents the Value2 value of the lower 2 rows