Calculating the DR or CR amount in the Suspense line vendor wise

[akumar2609]

I have the below dataset with vendor name in second last column. The vendor name column is not static and will keep on changing all the time. Some vendors will go away and some will get added every time.

There will be a suspense line (ref the Category column) with all the vendors. In this suspense line, "Entired Debit" and "Entired Credit" will be always zero. 

*ENTITY [..]	*NATURAL ACCOUNT [..]	*CENTER [..]	**Entered Debit	**Entered Credit	Line Description	Vendor	Category
2705	7400891009	21462B	666.64		Electronic	NETROADSHOW	ML
1339	7400891009	43W086	603.04		Electronic	NETROADSHOW	ML
7650	7907000008	32PAR2	0	0	Electronic	NETROADSHOW	Suspense
7504	7302827499	98HH33	443.82		Laptop	HP TECHNOLOGY	ML
7504	7302827499	98HZ92	882.82		Laptop	HP TECHNOLOGY	ML
7504	7302827499	98HZ49		207.25	Laptop	HP TECHNOLOGY	ML
7504	7302827499	98HN08	0	0	Laptop	HP TECHNOLOGY	Suspense
2705	74018007D2	214MFQ		42	Electronic	Telefonica	ML
7769	7400800008	62FR2N		34	Electronic	Telefonica	ML
2705	7400891009	21462B	555		Electronic	Telefonica	ML
1339	7400891009	43W086		9860	Electronic	Telefonica	ML
7650	7907000008	32PAR2	5569		Electronic	Telefonica	ML
7504	7302827499	98HZ49		214	Electronic	Telefonica	ML
2705	74018007D2	214ZXA	0	0	Electronic	Telefonica	Suspense

Requirement:-

I want Alteryx to separate the data based on the vendors and calculate the **Entered Debit or **Entered Credit amount as applicable and give me in the suspense line. I also want all the output should in one excel file but in different tabs based on the vendor names. In the above example. There should be three tabs (as there are three vendor this time) and tab name should be on vendor names. Refer the below required result section.

First Result:- NETROADSHOW Vendor

Required Result.
*ENTITY [..]	*NATURAL ACCOUNT [..]	*CENTER [..]	**Entered Debit	**Entered Credit	Line Description	Vendor	Category
2705	7400891009	21462B	666.64		Electronic	NETROADSHOW	ML
1339	7400891009	43W086	603.04		Electronic	NETROADSHOW	ML
7650	7907000008	32PAR2		1269.68	Electronic	NETROADSHOW	Suspense

Second Result:- HP TECHNOLOGY Vendor

*ENTITY [..]	*NATURAL ACCOUNT [..]	*CENTER [..]	**Entered Debit	**Entered Credit	Line Description	Vendor	Category
7504	7302827499	98HH33	443.82		Laptop	HP TECHNOLOGY	BANA
7504	7302827499	98HZ92	882.82		Laptop	HP TECHNOLOGY	BANA
7504	7302827499	98HZ49		207.25	Laptop	HP TECHNOLOGY	ML
7504	7302827499	98HN08		1119.39	Laptop	HP TECHNOLOGY	Suspense

Third Result:- Telefonica Vendor

*ENTITY [..]	*NATURAL ACCOUNT [..]	*CENTER [..]	**Entered Debit	**Entered Credit	Line Description	Vendor	Category
2705	74018007D2	214MFQ		42	Electronic	Telefonica	ML
7769	7400800008	62FR2N		34	Electronic	Telefonica	BANA
2705	7400891009	21462B	555		Electronic	Telefonica	BANA
1339	7400891009	43W086		9860	Electronic	Telefonica	BANA
7650	7907000008	32PAR2	5569		Electronic	Telefonica	BANA
7504	7302827499	98HZ49		214	Electronic	Telefonica	ML
2705	74018007D2	214ZXA	4026		Electronic	Telefonica	Suspense

[Bren_Spill]

@akumar2609 - a Multi-Row tool works well here. You can create a running balance and pull the endig balance from the ML records into the suspense line. See attached.

[Bren_Spill · Accepted solution]

Correction to the DR formula: