I have 2 tables, both have the same format, the data in each table is added by someone else. Each table has the same headings
Due Action Debit/Credit Balance where the Balance column is a calculation of Debit/Credit + previous Balance (=F??+[@[Debit/Credit]]).
To combine the tables I use the following function =SORT(VSTACK(Table1,Table2),1,1,FALSE). This works fine except that the Balance column is not a calculation with the new table but of each individual table. Is there any way to get the Balance to be the sum of the Debit/Credit of the combined table?
In advance, thanks for your help
Hi Sam,
As you have noticed, VSTACK (as does HSTACK) doesn't keep the formulas. It merely stacks the arrays as they are. You'd have to add a few extra steps. Since you have VSTACK, you have other DA functions at your disposal as well. The formula looks like this.
=LET(
stck, SORT(VSTACK(Table1,Table2),1,1,FALSE),
data, DROP(stck,,-1),
dc, TAKE(data,,-1),
rt, MMULT(--(SEQUENCE(COUNT(dc))>=SEQUENCE(,COUNT(dc))),dc),
result, HSTACK(data,rt),
result
)
In words, what this does is:
1) VSTACK both tables and SORT them (stck, that's your original formula)
2) DROP the last column (data)
3) TAKE the D/C column (dc)
4) Calculate the running total for D/C (rt)
5) HSTACK step 2 and 4 (result)
Step 4 is a subject by itself and you would need to study the MMULT function if you are not familiar with it.
The attached workbook contains a working example and I trust you are able to apply the same technique in your own. I'm open to hear about more effective ways to do this, as I can't think of any other solution just now.
You could use SCAN for the running total:
rt, SCAN(0,dc,LAMBDA(a,b,a+b))
Good point! Thx.
@Velouria
Thank you both, the solution works perfectly. Now I have to understand what you did 
The best way to analyze/understand a LET function is to see what every single step does. When you take my formula, replace the last word "return" with "stck" and press enter.
Now you see what the first step produces. That's your initial formula, stacking and sorting the two arrays.
Next, put "data" in as the last word (i.e. variable) of the formula. You'll see the stck-array with the last column removed.
Do that for the other variables as well, and see what each step does.
Once again, thank you, great tip for someone learning LET
@Velouria
Don't want to forget you in my appreciation, the SCAN is a lot easier to understand than MMULT as I still struggle with conceptualizing arrays
No worries - I confess my eyes tend to glaze over if I see MMULT in a formula. 😉
