|
running balance
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. |
running balance
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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
running balance
use pivot table for better results
On Oct 22, 11:20*am, Eliz wrote: 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. |
running balance
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. "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
running balance
i want to try to use pivot tables, but i am really not familiar with it.
"muddan madhu" wrote: use pivot table for better results On Oct 22, 11:20 am, Eliz wrote: 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. . |
running balance
Use this formula in H2
=SUMIF(D:D,D2,G:G)-SUMIF(D: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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
running balance
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" wrote: Use this formula in H2 =SUMIF(D:D,D2,G:G)-SUMIF(D: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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
running balance
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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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" wrote: Use this formula in H2 =SUMIF(D:D,D2,G:G)-SUMIF(D: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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
running balance
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" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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" wrote: Use this formula in H2 =SUMIF(D:D,D2,G:G)-SUMIF(D: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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
running balance
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 DataPivot Table and Pivot Char ReportNextNextLayout 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 clickPASTE SPECIALValues. 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 formulas 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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" wrote: Use this formula in H2 =SUMIF(D:D,D2,G:G)-SUMIF(D: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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
running balance
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 "Ms-Exl-Learner" wrote: 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 DataPivot Table and Pivot Char ReportNextNextLayout 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 clickPASTE SPECIALValues. 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 formulas 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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" wrote: Use this formula in H2 =SUMIF(D:D,D2,G:G)-SUMIF(D: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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. "Ms-Exl-Learner" wrote: 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! -------------------- (Ms-Exl-Learner) -------------------- "Eliz" wrote: 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. |
| All times are GMT +1. The time now is 04:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com