Problem description & analysis:
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 a semicolon. If it is a 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 |
Solution:
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)
Explanation:
E@b converts an Excel table to a sequence. ~(1) represents the 1st child member of the current member in a sequence; $[] represents a string.
For more related SPL XLL information:
SPL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso