# Split each row into multiple rows according to the specified rule

Here is an Excel table:

 A B C D 1 CreditBankCode BeneficiaryName Narration Amount 2 011 BENSON MATHIAS April 2024 Salary 72558.95 3 057 SAIFULLAHI RABIU April 2024 Salary 45000 4 076 ABDULKADIR AUWALU April 2024 Salary 55000 5 001 001ABDULKADIR April 2024 Salary 85000 6 002 002ABDULKADIR April 2024 Salary 105000 7 003 003ABDULKADIR April 2024 Salary 115000

We need to split each row into multiple rows. The rule is like this: if Amount value is less than 50000, do not split the row; if Amount value is greater than 50000, split the row by creating a new row every 30000. Below is the expected result:

 A B C D 10 CreditBankCode BeneficiaryName Narration Amount 11 011 BENSON MATHIAS April 2024 Salary 30000 12 011 BENSON MATHIAS April 2024 Salary 42558.95 13 057 SAIFULLAHI RABIU April 2024 Salary 45000 14 076 ABDULKADIR AUWALU April 2024 Salary 30000 15 076 ABDULKADIR AUWALU April 2024 Salary 25000 16 001 001ABDULKADIR April 2024 Salary 30000 17 001 001ABDULKADIR April 2024 Salary 30000 18 001 001ABDULKADIR April 2024 Salary 25000 19 002 002ABDULKADIR April 2024 Salary 30000 20 002 002ABDULKADIR April 2024 Salary 30000 21 002 002ABDULKADIR April 2024 Salary 45000 22 003 003ABDULKADIR April 2024 Salary 30000 23 003 003ABDULKADIR April 2024 Salary 30000 24 003 003ABDULKADIR April 2024 Salary 30000 25 003 003ABDULKADIR April 2024 Salary 25000

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)

E@b function removes table titles. news()function split 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.