"In the following Excel table, rows having same Block values belong to the same group. A B 1 Bloc .."

blackduckie RaqForum 28 No.
1 Reply • 456 View • 3 Years ago

Get the Previous & Next Row of Same Group without Sorting the Excel Data

excel(115)

In the following Excel table, rows having same Block values belong to the same group.

	A	B
1	Block	Bench
2	4	1
3	5	1
4	6	1
5	4	8
6	5	8
7	6	8
8	4	9
9	5	9
10	6	9
11	4	5
12	5	5
13	6	5

Task: Calculate two new columns. BenchAbove is the Bench in the previous row of the same group; if the current row is the first in the group, BenchAbove is empty. BenchBelow is the Bench in the next row of the same group; if the current row is the last in the group, BenchBelow is empty.

The expected result:

	A	B	C	D
1	Block	Bench	BenchAbove	BenchBelow
2	4	1		8
3	5	1		8
4	6	1		8
5	4	8	1	9
6	5	8	1	9
7	6	8	1	9
8	4	9	8	5
9	5	9	8	5
10	6	9	8	5
11	4	5	9
12	5	5	9
13	6	5	9

The difficulty of this type of computing tasks is that you need to get a value from both the previous row and the next row of the same group without altering the original data order.

1. Start esProc (Download esProc installation package and free DSK edition license raqsoft website. You will be prompted to load the license file when you run esProc for the first time).

2. Write a script and execute it

	A	B
1	=file("data.xlsx").xlsimport@t()	/Import the source Excel file
2	=A1.derive(~[:-1].select@1z(Block==A2.Block).Bench:BenchAbove, ~[1:].select@1(Block==A2.Block).Bench:BenchBelow)	/ Get the directly previous and next rows
3	=file("result.xlsx").xlsexport@(A2)	/ Export result to a new Excel file

In A2, ~[:-1] gets all rows before the current row; and ~[1:] gets all rows after the current row. select function does the searching task. @z option searches for the target from back to front; and @1 option enables finishing the searching once the first target member is found.

You can also get the task done within Excel using the clipboard according to the following directions:

1. Start esProc.

2. Open the to-be-computed file in Excel.

3. Select the area of A1:B13 and press Ctrl+C to paste it to the clipboard.

4. Back to esProc, select cell A1, and press Ctrl+V to paste data from the clipboard to A1. Make sure that the caret is blinking in cell A1(in its editing status) during the pasting.

undefined

5. Write the following esProc script to execute.

	A
1	….(Data pasted from the clipboard)
2	=A1.import@t()
3	=A2.new(~[:-1].select@1z(Block==A2.Block).Bench:BenchAbove, ~[1:].select@1(Block==A2.Block).Bench:BenchBelow)

6. Press F9 to execute the SQL script. Then you can click A3 to view the result in the value viewing section on the right. Press Shift (for not leaving the column headers behind) and click the “Copy data” button on the right-hand to paste A3’s result to clipboard.

undefined