Problem description & analysis:
Here is an Excel table:
We need to split each row into multiple rows. The rule is like this: if the Amount value is less than 50000, do not split the row; if the Amount value is greater than 50000, split the row by creating a new row every 30000. Below is the expected result:
Solution:
Enter the following formula in SPL XLL:
=spl("=E@b(?.news((t=~4,100.iterate@a(30000,,!if(t>50000,t-=30000))|t);?.~1,?.~2,?.~3,~))",A2:D7)
As shown in the picture below:
Explanation:
E@b function removes table titles. news()function splits each row into multiple rows according to the specified rule; 100.iterate performs the iteration 100 times repeatedly and stops the iteration as long as the specified condition is met (suppose one row is split into 100 rows at most, and increase the number as needed); ~ is the current member of the sequence, ~1 is the 1st child member of the current member; and symbol | concatenates sequences.
The example was originally on Reddit. Feel free to compare the conventional solutions with the SPL approach.
If you are ready to simplify? Give it a try with the links below: 👇
SPL download address: esProc Desktop FREE Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
YouTube FREE courses: SPL Programming
