Find adjacent rows in the same category
In the following Excel, rows with the same Block belong to the same group.
Goal: Calculate two new columns; BenchAbove is the Bench of the previous row in the same group; if this row is the first row, BenchAbove is empty. BenchBelow is the Bench of the next row in the same group. If this row is the last row, BenchBelow is empty. The results are as follows:
The difficulty of this type of problem is to get the previous line and the next line in the same group without changing the line order.
Write SPL script:
A1 Read Excel data
A2 Get adjacent rows, ~[:-1] gets all rows before the current row, ~[1:] is all rows after the current row, select is used for searching, @z means searching back, @1 Means that it ends when one record is found.
A3 export results to result.xlsx