14.4 Expand one row into multiple rows after splitting text

 

The following is a data table, in which columns D and E have multiple lines of text, the number of lines is the same, and such lines are in one-to-one correspondence. For example, F corresponds to Fail, as shown below:

imagepng

Now we want to split the values in column D, E by line break, and expand into multiple rows to make the result look like this:

imagepng

Script:

=spl("=E(?1).run(Grades=Grades.split(""\n""),Comment=Comment.split(""\n"")).news(Grades.len();Names,Class,Year,Grades(#):Grades,Comment(#):Comment)",A1:E4)

Loop through each row, split Grades and Comment into a string sequence by \n respectively, and then expand each row into multiple rows, the number of rows is the number of members of Grades sequence. In each new row, take the original Names, Class, and Year columns, the #th member of the Grades sequence is the Grades in new column, and the #th member of the Comment sequence is the Comment in new column, where # represents the row number expanded from original row.


esProc Desktop and Excel Processing
14.3 Expand one row into multiple rows based on value
14.5 Make up missing parts to make data continuous