Hi Just wondering if someone can help me find a way to Union an old data set with a new data set, pick up the closing balance of the old data set and update the running total for the new data set. Old Data Set date12Running Balance1/01/2018171.14 522,878.423/01/20183,000,000.00 3,522,878.421/02/2018820.75 3,523,699.171/03/2018835.91 3,524,535.089/03/20183,000,000.00 6,524,535.0826/03/2018 (600,000.00)5,924,535.081/04/20181,655.24 5,926,190.321/05/20181,645.33 5,927,835.658/05/20182,300,000.00 8,227,835.651/06/2018 (2,535.50)8,225,300.154/06/20182,400,000.00 10,625,300.1518/06/20181,300,000.00 11,925,300.151/07/20182,532.50 11,927,832.65 3,722.94 11,931,555.59 5,071.00 11,936,626.591/08/20184,145.55 11,940,772.1431/08/2018 (2,535.50)11,938,236.641/09/20185,759.85 11,943,996.4927/09/2018 (11,900,000.00)43,996.491/10/20184,466.65 48,463.143/10/20181,751,536.86 1,800,000.00 4,248,463.14 6,048,463.141/11/20182,805.67 6,051,268.811/12/20183,206.69 6,054,475.5017/12/2018 (1,800,000.00)4,254,475.50 New Data Set date121/01/20192499.9103/01/20190-35000004/01/20196000000017/01/20190-60000001/02/20191668.82014/02/2019350000001/03/20191367.2905/03/201913500000022/03/20190-110000001/04/20197090.24017/04/20190-60000001/05/20192156.2107/05/201910500000021/05/20190-110000001/06/20192968.0501/07/2019136.1901/08/2019147.6401/09/2019299.9601/10/20190-149.981/10/2019134.7801/11/2019142.6801/12/2019142.810 Output date12 Running Balance 1/01/2018171.14 522,878.423/01/20183,000,000.00 3,522,878.421/02/2018820.75 3,523,699.171/03/2018835.91 3,524,535.089/03/20183,000,000.00 6,524,535.0826/03/2018 (600,000.00) 5,924,535.081/04/20181,655.24 5,926,190.321/05/20181,645.33 5,927,835.658/05/20182,300,000.00 8,227,835.651/06/2018 (2,535.50) 8,225,300.154/06/20182,400,000.00 10,625,300.1518/06/20181,300,000.00 11,925,300.151/07/20182,532.50 11,927,832.65 3,722.94 11,931,555.59 5,071.00 11,936,626.591/08/20184,145.55 11,940,772.1431/08/2018 (2,535.50) 11,938,236.641/09/20185,759.85 11,943,996.4927/09/2018 (11,900,000.00) 43,996.491/10/20184,466.65 48,463.143/10/20181,751,536.86 1,800,000.00 4,248,463.14 6,048,463.141/11/20182,805.67 6,051,268.811/12/20183,206.69 6,054,475.5017/12/2018 (1,800,000.00) 4,254,475.501/01/20192499.910 4,256,975.413/01/20190-3500000- 3,500,000.004/01/201960000000 6,000,000.0017/01/20190-6000000- 6,000,000.001/02/20191668.820 1,668.8214/02/201935000000 3,500,000.001/03/20191367.290 1,367.295/03/2019135000000 13,500,000.0022/03/20190-11000000- 11,000,000.001/04/20197090.240 7,090.2417/04/20190-6000000- 6,000,000.001/05/20192156.210 2,156.217/05/2019105000000 10,500,000.0021/05/20190-11000000- 11,000,000.001/06/20192968.050 2,968.051/07/2019136.190 136.191/08/2019147.640 147.641/09/2019299.960 299.961/10/20190-149.98- 149.981/10/2019134.780 134.781/11/2019142.680 142.681/12/2019142.810 142.81 Initial thoughts are a union and then a multirow formula but i am not sure i have the formula quite correct. test data.xlsx

Running Total from Previous Closing Balance

Just wondering if someone can help me find a way to Union an old data set with a new data set, pick up the closing balance of the old data set and update the running total for the new data set.

Old Data Set

date	1	2	Running Balance
1/01/2018	171.14		522,878.42
3/01/2018	3,000,000.00		3,522,878.42
1/02/2018	820.75		3,523,699.17
1/03/2018	835.91		3,524,535.08
9/03/2018	3,000,000.00		6,524,535.08
26/03/2018		(600,000.00)	5,924,535.08
1/04/2018	1,655.24		5,926,190.32
1/05/2018	1,645.33		5,927,835.65
8/05/2018	2,300,000.00		8,227,835.65
1/06/2018		(2,535.50)	8,225,300.15
4/06/2018	2,400,000.00		10,625,300.15
18/06/2018	1,300,000.00		11,925,300.15
1/07/2018	2,532.50		11,927,832.65
	3,722.94		11,931,555.59
	5,071.00		11,936,626.59
1/08/2018	4,145.55		11,940,772.14
31/08/2018		(2,535.50)	11,938,236.64
1/09/2018	5,759.85		11,943,996.49
27/09/2018		(11,900,000.00)	43,996.49
1/10/2018	4,466.65		48,463.14
3/10/2018	1,751,536.86		1,800,000.00
	4,248,463.14		6,048,463.14
1/11/2018	2,805.67		6,051,268.81
1/12/2018	3,206.69		6,054,475.50
17/12/2018		(1,800,000.00)	4,254,475.50

New Data Set

date	1	2
1/01/2019	2499.91	0
3/01/2019	0	-3500000
4/01/2019	6000000	0
17/01/2019	0	-6000000
1/02/2019	1668.82	0
14/02/2019	3500000	0
1/03/2019	1367.29	0
5/03/2019	13500000	0
22/03/2019	0	-11000000
1/04/2019	7090.24	0
17/04/2019	0	-6000000
1/05/2019	2156.21	0
7/05/2019	10500000	0
21/05/2019	0	-11000000
1/06/2019	2968.05	0
1/07/2019	136.19	0
1/08/2019	147.64	0
1/09/2019	299.96	0
1/10/2019	0	-149.98
1/10/2019	134.78	0
1/11/2019	142.68	0
1/12/2019	142.81	0

Output

date	1	2	Running Balance
1/01/2018	171.14		522,878.42
3/01/2018	3,000,000.00		3,522,878.42
1/02/2018	820.75		3,523,699.17
1/03/2018	835.91		3,524,535.08
9/03/2018	3,000,000.00		6,524,535.08
26/03/2018		(600,000.00)	5,924,535.08
1/04/2018	1,655.24		5,926,190.32
1/05/2018	1,645.33		5,927,835.65
8/05/2018	2,300,000.00		8,227,835.65
1/06/2018		(2,535.50)	8,225,300.15
4/06/2018	2,400,000.00		10,625,300.15
18/06/2018	1,300,000.00		11,925,300.15
1/07/2018	2,532.50		11,927,832.65
	3,722.94		11,931,555.59
	5,071.00		11,936,626.59
1/08/2018	4,145.55		11,940,772.14
31/08/2018		(2,535.50)	11,938,236.64
1/09/2018	5,759.85		11,943,996.49
27/09/2018		(11,900,000.00)	43,996.49
1/10/2018	4,466.65		48,463.14
3/10/2018	1,751,536.86		1,800,000.00
	4,248,463.14		6,048,463.14
1/11/2018	2,805.67		6,051,268.81
1/12/2018	3,206.69		6,054,475.50
17/12/2018		(1,800,000.00)	4,254,475.50
1/01/2019	2499.91	0	4,256,975.41
3/01/2019	0	-3500000	- 3,500,000.00
4/01/2019	6000000	0	6,000,000.00
17/01/2019	0	-6000000	- 6,000,000.00
1/02/2019	1668.82	0	1,668.82
14/02/2019	3500000	0	3,500,000.00
1/03/2019	1367.29	0	1,367.29
5/03/2019	13500000	0	13,500,000.00
22/03/2019	0	-11000000	- 11,000,000.00
1/04/2019	7090.24	0	7,090.24
17/04/2019	0	-6000000	- 6,000,000.00
1/05/2019	2156.21	0	2,156.21
7/05/2019	10500000	0	10,500,000.00
21/05/2019	0	-11000000	- 11,000,000.00
1/06/2019	2968.05	0	2,968.05
1/07/2019	136.19	0	136.19
1/08/2019	147.64	0	147.64
1/09/2019	299.96	0	299.96
1/10/2019	0	-149.98	- 149.98
1/10/2019	134.78	0	134.78
1/11/2019	142.68	0	142.68
1/12/2019	142.81	0	142.81

Initial thoughts are a union and then a multirow formula but i am not sure i have the formula quite correct.

test data.xlsx

Workflow

Accepted answers

grazitti_sapna

Hi @N89 , give this a try and let me know if this works for you.

If this is what you are looking for kindly mark this post as a solution.

Thanks

Running Total from Previous Closing Balance-GS.yxmd

jdunkerley79

I'd suggest:

The multi-field formula tool is used to tidy Set 1's numeric value: converting from string to double with an expression of:

IIF(REGEX_Match([_CurrentField_],"\s*"),NULL(),ToNumber(Replace(REGEX_Replace([_CurrentField_],"\((.*)\)","-$1"),",","")))

Next, you pick the last value of this set

Using an append field tool add the last Running Total to Set 2

You can then use a multi-row formula tool to fill it downward. Set so missing rows are null and then update Running Total with:

IIF(IsNull([Row-1:Running Balance]),[Running Balance],[Row-1:Running Balance])+[1]+[2]

Finally, a Union tool with a fixed order of inputs joins the 2 sets together.

Union 2 sets.yxmd

All comments