"The following table records someone’s answers to a set of questions: D E 1 Form Question Form An .."

jinxing RaqForum 41 No.
147 View • 4 Months ago

Split each cell value and expand it according to the specified rule

The following table records someone’s answers to a set of questions:

	D	E
1	Form Question	Form Answer
2	1	All of the Above
3	2	A;B;C
4	3	B;C
5	4	All of the Above

An answer generally consists of options separated by the semicolon. If it is string “All of the Above”, it has all options under the same question number in dictionary table Sheet2.

	A	B
1	1	A
2	1	b
3	1	c
4	1	d
5	2	a
6	2	b
7	2	c
8	2	d
9	3	a
10	3	b
11	3	c
12	3	d
13	4	a
14	4	b
15	4	c
16	4	d
17	4	e

We need to split each answer into individual options, as shown below:

	A	B
1	Question	What I want
2	1	A
3	1	b
4	1	c
5	1	d
6	2	A
7	2	B
8	2	C
9	3	B
10	3	C
11	4	a
12	4	b
13	4	c
14	4	d
15	4	e

Use SPL XLL to enter the following formula:

=spl("=dt=?1,dc=?2,E@b(dt.news(if(~(2)==$[All of the Above],dc.select(~(1)==dt.~(1)).(~(2)), ~(2).split($[;]));dt.~(1),~))",D2:E5,Sheet2!A2:Sheet2!B18)

Picture1png