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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
