running balance

[Eliz]

how can i create a running balance when i have a real big data?

column a date
column b dept
column c doc no.
column d product code
column e product descriptiom
column f issue
column g received
column h balance

if i hv a really big database, how can i calculate the balance of each
product when i key in the received or issue figure?

Please help.

Thank you.

 

[Ms-Exl-Learner]

Try this…

Copy and paste this formula in I column

If you want to subtract the Received Total from Issue Total then use this
formula
=SUM(G:G)-SUM(F:F)
If you want to subtract the Issue Total from Received Total then use this
formula
=SUM(F:F)-SUM(G:G)

OR

If you want to subtract the each Issue and Received values nearby the cells
then try this in B2

Subtract Received from Issue
=G2-F2
Subtract Issue from Received
=F2-G2
To total the Balance try this
=SUM(H:H)

Copy and paste this formula for the remaining cell.

Hope this will help you.

If this post helps, Click Yes!

 

[muddan madhu]

use pivot table for better results

 

[Eliz]

Is there a formula that i can set that when i key in the product code n
amount of issue/received, it will automatically search for the same product n
calculate the balance for me?

Please let me know.. really desperate abt this.

Thank you.

 

[Eliz]

i want to try to use pivot tables, but i am really not familiar with it.

 

[Ms-Exl-Learner]

Use this formula in H2

=SUMIF(D,D2,G:G)-SUMIF(D,D2,F:F)

Copy and paste it for the remaining cells of H Column to know the Balance of
Every Products.

If this post helps, Click Yes!

 

[Eliz]

thanks the formulas works. But i just want to know if instead getting the
full figures in the whole worksheet.. is it posible to get the balance each
transaction instead of the whole lump sum? Please help.

Thank you.

Eliz

 

[Ms-Exl-Learner]

Copy the below formula and locate the cursor in H2 and paste it.

=SUMIF($D$1:$D2,$D2,$G$1:$G2)-SUMIF($D$1:$D2,$D2,$F$1:$F2)

After pasting the formula in H2 now you can copy the H2 cell and paste it to
the remaining cells.

Keep in mind that you can paste the above formula other than H column also,
but the pasting cell should be the 2nd row. For example if you want to paste
it in J column then you have to copy and paste it in J2 cell only. After
that you should copy the J2 cell and apply it for the remaining cells.

I think I have explained it clearly.

If this post helps, Click Yes!

 

[Eliz]

Thanks. It really help. But i still hv to trouble you with more question.

how do i get the final balance of all the transaction? how do i make it
show the last balance instead of filtering all the transaction?? i would like
to get the summary of the last balance .

Thank you.

Eliz

 

[Ms-Exl-Learner]

Thanks for feeding back and solution for the query you have asked for is
given below.

For Excel 2003
Select the D Column and go to Data>Pivot Table and Pivot Char
Report>Next>Next>Layout
In right of that popup window you can see a Field that is Product Code, Now
click and hold the mouse and drag it to Row. In the same manner do it once
again and take it in Data field also. Now both in Row and Data you can see
the field Product Code. Give ok then click Finish.

Now a new sheet is added and you will see the Product Codes without any
duplication. Just select the product codes and do copy. Now insert a new
sheet and place the cursor in A2 cell and do right click>PASTE SPECIAL>Values.

Now in A1 type Product Code
And in B1 type Issue
And in C1 type Received
And in D1 type Balance

In B2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$F:$F)

In C2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$G:$G)

In D2 paste this formula
=SUMIF(Sheet1!$D:$D,$A2,Sheet1!$G:$G)-SUMIF(Sheet1!$D:$D,$A2,Sheet1!$F:$F)
Or
=C2-B2

After applying the formula’s in the above cells now copy B2, C2 & D2 cells
and paste it to the remaining cells.

Note that your master data should be in Sheet1 worksheet, if it is not in
sheet1 worksheet then change the sheet name (Sheet1) in the above formula.

For any further clarifications please let us know.

If this post helps, Click Yes!

 

[Eliz]

Will try the pivot table.. but if i use the pivot table, can i still copy teh
last figure to a new sheet for the new mth?

we are actually trying to use the worksheet as stock card. so, we need to
have the final figure at the end to show the begining of the new mth.. Please
advise.

Thank you.

Eliz