"In the following Excel table, each of odd numbered rows contains unduplicated key words in all i .."

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

Convert a Multi-row-based Table to a Regular Row-wise Table

excel(115) order-based calculation(12) transpose(9)

In the following Excel table, each of odd numbered rows contains unduplicated key words in all its cells and each even numbered row contains values corresponding to the key words in all cells.

	A	B	C	D	E
1	A	H	M	B	T
2	39	2	14	9	20
3	F	W	P	L	C
4	6	35	11	4	22
5	Y	D	V	E	U
6	26	5	7	18	27

Task: Convert the table to a regular two-column row-wise table, where column 1 contains key words and column 2 contains the corresponding values. The expected result table is as follows:

	A	B
1	A	39
2	H	2
3	M	14
4	B	9
5	T	20
6	F	6
7	W	35
8	P	11
9	L	4
10	C	22
11	Y	26
12	D	5
13	V	7
14	E	18
15	U	27

Directions:

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

2. Open the to-be-computed file in Excel, select the area of A1:E6, and press Ctrl+C to copy it to the clipboard.

3. Open esProc, select cell A1, and press Ctrl+V to paste data from the clipboard to A1. Make sure that the caret is placed above A1 in its editing status during the pasting.

undefined

4. Write the following script in esProc.

	A
1	…(Data pasted from the clipboard）
2	=A1.split@n("\t")
3	=A2.step(2,1).conj()
4	=A2.step(2,2).conj()
5	=A3.(~+"\t"+A4(#)).concat@n()

In this script, step(2,1) gets all odd numbered rows. conj() concatenates values of the rwos into a single-column table. Thus A3 gets a column of key words and A4 gets a column of values. A5 then joins them together, during which carriage return and tab are used to separate rows and columns respectively.

5. Press F9 to execute the SQL script. Then you can click A5 to view the result in the value viewing section on the right. Click the “Copy data” button on the right-hand to paste A5’s result to clipboard.

undefined