sum or subtract for the same cell based on match column between two sheets

Omran Y

Board Regular
Joined
Jul 17, 2023
Messages
60
Office Version
  1. 2013
Platform
  1. Windows
Hello
I want match names in column C for EMPLOYEE sheets with column B for TT sheet if the column C in TT sheet contains Advance payment then should subtract amount for adjacent cell for the name in EMPLOYEE sheet from amount for adjacent cell for the word Advance payment
for instance OMAR ALI OMAR =-20000-2000=-22000 as show in third picture as highlighted cell in D2 and change adjacent cell for NET AMOUNT by sum amount for adjacent NAME and adjacent cell for SALARY to become -22000+3000= -19000.
if the column C in TT sheet contains Pay payment then should sum amount for adjacent cell for the name in EMPLOYEE sheet from amount for adjacent cell for the word Pay payment
for instance AHMED OMAR ALI=-2000+3000=1000 as show in last sheet as highlighted cell in D8 and change adjacent cell for NET AMOUNT by sum amount for adjacent NAME and adjacent cell for SALARY to become 1000+3000= 4000.
and when repeat the same name in TT sheet then should move to next row when add new data as in pic2 and pic3 without repeat calculation amounts have ever sum or subtract( the next file will depend on last amount for previous file) .
the result should be in EMPLOYEE sheet in column D for adjacent cells NAME & NET AMOUNT
I highlighted the changed cells in third picture for each case when change data in TT sheet .
also posted here
sum or subtract for the same cell based on match column between two sheets
case1

1.xlsm
ABCD
1START DATELAST DATENAMEFIRST BALANCE
201/01/202331/01/2023OMAR ALI OMAR-20,000.00
3SALARY3,000.00
4NET AMOUNT-17,000.00
5
6
7START DATELAST DATENAMEFIRST BALANCE
801/01/202331/01/2023AHMED OMAR ALI-2,000.00
9SALARY3,000.00
10NET AMOUNT1,000.00
11
12
13START DATELAST DATENAMEFIRST BALANCE
1401/01/202331/01/2023ALI MAHMUD OMAR1000
15SALARY2500
16NET AMOUNT3500
EMPLOYEES



1.xlsm
ABCD
211/02/2023OMAR ALI OMARAdvance payment by safe2,000.00
312/02/2023ALI MAHMUD OMARAdvance payment by bank2,500.00
413/02/2023AHMED OMAR ALIPay payment safe3,000.00
TT




what I want in EMPLOYEES sheet
1.xlsm
ABCD
1START DATELAST DATENAMEFIRST BALANCE
201/01/202331/01/2023OMAR ALI OMAR-22000
3SALARY3000
4NET AMOUNT-19000
5
6
7START DATELAST DATENAMEFIRST BALANCE
801/01/202331/01/2023AHMED OMAR ALI1000
9SALARY3000
10NET AMOUNT4000
11
12
13START DATELAST DATENAMEFIRST BALANCE
1401/01/202331/01/2023ALI MAHMUD OMAR-1500
15SALARY2500
16NET AMOUNT1000
EMPLOYEES
Cell Formulas
RangeFormula
D16D16=D14+D15


case2
2.xlsm
ABCD
1DATENAMEDETAILSAMOUNT
211/02/2023OMAR ALI OMARAdvance payment by safe2,000.00
312/02/2023ALI MAHMUD OMARAdvance payment by bank2,500.00
413/02/2023AHMED OMAR ALIPay payment safe3,000.00
514/03/2023OMAR ALI OMARPay payment bank5,000.00
TT


what I want in EMPLOYEES sheet based on last updating in CASE1 above
2.xlsm
ABCD
1START DATELAST DATENAMEFIRST BALANCE
201/01/202331/01/2023OMAR ALI OMAR-17000
3SALARY3000
4NET AMOUNT-14000
5
6
7START DATELAST DATENAMEFIRST BALANCE
801/01/202331/01/2023AHMED OMAR ALI1000
9SALARY3000
10NET AMOUNT4000
11
12
13START DATELAST DATENAMEFIRST BALANCE
1401/01/202331/01/2023ALI MAHMUD OMAR-1500
15SALARY2500
16NET AMOUNT1000
EMPLOYEES



case 3
3.xlsm
ABCD
1DATENAMEDETAILSAMOUNT
211/02/2023OMAR ALI OMARAdvance payment by safe2,000.00
312/02/2023ALI MAHMUD OMARAdvance payment by bank2,500.00
413/02/2023AHMED OMAR ALIPay payment safe3,000.00
514/03/2023OMAR ALI OMARPay payment bank5,000.00
614/03/2023OMAR ALI OMARPay payment bank2,000.00
715/03/2023ALI MAHMUD OMARPay payment safe3,200.00
TT


what I want in EMPLOYEES sheet based on last updating in CASE2 above
3.xlsm
ABCD
1START DATELAST DATENAMEFIRST BALANCE
201/01/202331/01/2023OMAR ALI OMAR-15000
3SALARY3000
4NET AMOUNT-12000
5
6
7START DATELAST DATENAMEFIRST BALANCE
801/01/202331/01/2023AHMED OMAR ALI1000
9SALARY3000
10NET AMOUNT4000
11
12
13START DATELAST DATENAMEFIRST BALANCE
1401/01/202331/01/2023ALI MAHMUD OMAR1700
15SALARY2500
16NET AMOUNT4200
EMPLOYEES

I hope to find answering here for this project
 
I appreciate for your, time and help .
just I want last thing . indeed I posted details are not logic . when I run the macro every time will continue subtract or some repeatedly , that's wrong
so I don't find any way to solve that except one thing and it's should add another condition for TT sheet if the date today in column A then will sum or subtract for each name but if there are old dates(not today) then will ignore totally , is the condition possible to add your code?
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I appreciate for your
With that I understand that the original requirement is covered?


just I want last thing . indeed I posted details are not logic . when I run the macro every time will continue subtract or some repeatedly , that's wrong
so I don't find any way to solve that except one thing and it's should add another condition for TT sheet if the date today in column A then will sum or subtract for each name but if there are old dates(not today) then will ignore totally , is the condition possible to add your code?
I'd be happy to help you, but that condition is not in the OP.
Please create a new thread and explain what you need. Your examples are very good.
 
Upvote 0
With that I understand that the original requirement is covered?
not completely , sorry !
you don't seem to note the amounts for the some names will keep without any changing again as I posted in OP
when add new data as in pic2 and pic3 without repeat calculation amounts have ever sum or subtract( the next file will depend on last amount for previous file)
doesn't calculate again for the same amounts and the same names have ever calculated
based on OP

Z Microsoft Excel ج.xlsx
ABCD
1START DATELAST DATENAMEFIRST BALANCE
201/01/202331/01/2023OMAR ALI OMAR-22000
3SALARY3000
4NET AMOUNT-19000
5
6
7START DATELAST DATENAMEFIRST BALANCE
801/01/202331/01/2023AHMED OMAR ALI1000
9SALARY3000
10NET AMOUNT4000
11
12
13START DATELAST DATENAMEFIRST BALANCE
1401/01/202331/01/2023ALI MAHMUD OMAR-1500
15SALARY2500
16NET AMOUNT1000
EMPLOYEES
Cell Formulas
RangeFormula
D16D16=D14+D15



Z Microsoft Excel.xlsx
ABCD
1DATENAMEDETAILSAMOUNT
211/02/2023OMAR ALI OMARAdvance payment by safe2000
312/02/2023ALI MAHMUD OMARAdvance payment by bank2500
413/02/2023AHMED OMAR ALIPay payment safe3000
514/03/2023OMAR ALI OMARPay payment bank5000
TT



result
Z Microsoft Excel .xlsx
ABCD
1START DATELAST DATENAMEFIRST BALANCE
201/01/202331/01/2023OMAR ALI OMAR-17000
3SALARY3000
4NET AMOUNT-14000
5
6
7START DATELAST DATENAMEFIRST BALANCE
801/01/202331/01/2023AHMED OMAR ALI1000
9SALARY3000
10NET AMOUNT4000
11
12
13START DATELAST DATENAMEFIRST BALANCE
1401/01/202331/01/2023ALI MAHMUD OMAR-1500
15SALARY2500
16NET AMOUNT1000
EMPLOYEES

as to names AHMED OMAR ALI,ALI MAHMUD OMAR the amounts will keep as the picture1 withoiut change again , you say why?! because it has ever calculated when the data in TT sheet as in picture2 in OP . it's not right when change the amounts again . you can compare the pic 1 with pic3 in this post will keep amounts for names AHMED OMAR ALI,ALI MAHMUD OMAR without any change again(will ignore amounts in row2,3,4 in TT sheet when calculate) , this is the right way and what I want .
so I thought this way is not possible to do that by code . you know more than me . that's why I suggest
it's should add another condition for TT sheet if the date today in column A then will sum or subtract for each name but if there are old dates(not today) then will ignore totally , is the condition possible to add your code?
and of course that condition is not in the OP when I see you don't note this
when add new data as in pic2 and pic3 without repeat calculation amounts have ever sum or subtract( the next file will depend on last amount for previous file)
I suggest for you using DATE in column A
Please create a new thread and explain what you need. Your examples are very good.
if you still this different totally thread then I will add new thread today and I hope to help me to complete this part of my project.
I would your comment soon .
thanks again
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,809
Messages
6,181,076
Members
453,020
Latest member
mattg2448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top