Split Each Cell Value And Expand It According To The Specified Rule

Problem description & analysis:

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

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)

As shown in the picture below:

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.