Add column in Excel based on the value of other columns-CodePudding

I am pretty new to using Excel and am unaware of its features.

I have data in an excel sheet having 4 columns (customer_id, customer_name, amount_debit, amount_debit).

customer_id customer_name   amount_credit   amount_debit    prev_bal    curr_bal
1053        Bhavya Gupta        1000            0             1000        2000
1053        Bhavya Gupta        800             0             200         1000
298          prerna             0               100            0          -100
1053        Bhavya Gupta        200             0              0           200

I need to add 2 new columns (which are prev_bal and curr_bal) to the Excel sheet.

The prev_bal and curr_bal should be calculated uniquely for each customer based on its customer id. Is there any way that I can achieve this using script or excel features.

CodePudding user response：

One approach would be using dynamic references to ranges. It's kind of complex but at least it get the output you've posted:

Formula in column E2 is:

=IFERROR(INDEX(F3:$F$6;MATCH(A2;A3:$A$6;0));0)

Formula in column F2 is:

=SUMIF(A2:$A$5;A2;C2:$C$5)-SUMIF(A2:$A$5;A2;D2:$D$5)

Drag both of them to bottom

Please, note my argument separator in functions is the semicolon, depending on your regional settings it might be a comma.

Anyways, I've uploaded the workbook to Gdrive so you can download and check the formulas and test it properly:

https://docs.google.com/spreadsheets/d/1rkr6EUHfR7QksDShMlkS97nZBD7e73OV/edit?usp=sharing&ouid=114417674018837700466&rtpof=true&sd=true