This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-06-28T07:25:11+00:00) Split Each Cell Value And Expand It According To The Specified Rule. Retrieved from https://www.scien.cx/2024/06/28/split-each-cell-value-and-expand-it-according-to-the-specified-rule/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.