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

[Omran Y]

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
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-20,000.00
3			SALARY	3,000.00
4			NET AMOUNT	-17,000.00
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	-2,000.00
9			SALARY	3,000.00
10			NET AMOUNT	1,000.00
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	1000
15			SALARY	2500
16			NET AMOUNT	3500
EMPLOYEES

1.xlsm
	A	B	C	D
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2,000.00
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2,500.00
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3,000.00
TT

what I want in EMPLOYEES sheet

1.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-22000
3			SALARY	3000
4			NET AMOUNT	-19000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

Cell Formulas
Range	Formula
D16	D16	=D14+D15

case2

2.xlsm
	A	B	C	D
1	DATE	NAME	DETAILS	AMOUNT
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2,000.00
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2,500.00
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3,000.00
5	14/03/2023	OMAR ALI OMAR	Pay payment bank	5,000.00
TT

what I want in EMPLOYEES sheet based on last updating in CASE1 above

2.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-17000
3			SALARY	3000
4			NET AMOUNT	-14000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

case 3

3.xlsm
	A	B	C	D
1	DATE	NAME	DETAILS	AMOUNT
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2,000.00
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2,500.00
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3,000.00
5	14/03/2023	OMAR ALI OMAR	Pay payment bank	5,000.00
6	14/03/2023	OMAR ALI OMAR	Pay payment bank	2,000.00
7	15/03/2023	ALI MAHMUD OMAR	Pay payment safe	3,200.00
TT

what I want in EMPLOYEES sheet based on last updating in CASE2 above

3.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-15000
3			SALARY	3000
4			NET AMOUNT	-12000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	1700
15			SALARY	2500
16			NET AMOUNT	4200
EMPLOYEES

I hope to find answering here for this project

 

[DanteAmor]

Try this:

Sub sum_or_subtract()
  Dim f As Range, c As Range
  Dim det As String
  Dim v As Double
   
  det = LCase("Advance payment")
  For Each c In Sheets("TT").Range("B2", Sheets("TT").Range("B" & Rows.Count).End(3))
    Set f = Sheets("EMPLOYEES").Range("C:C").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      v = c.Offset(, 2).Value * IIf(Left(LCase(c.Offset(, 1).Value), Len(det)) = det, -1, 1)
      f.Offset(0, 1).Value = f.Offset(0, 1).Value + v
      f.Offset(2, 1).Value = f.Offset(0, 1).Value + f.Offset(1, 1).Value
    End If
  Next
End Sub

 

[Omran Y]

Hi,
I'm not sure what my bad , but the code doesn't do anything and there is no error !

 

[DanteAmor]

Check the name on both sheets, they must be exactly the same, check that they do not have spaces before or after, just like your examples.

 

[Omran Y]

ok I copy in new file works but not completely
I would compare result in pic 5 based on pic4 in OP with what I got based on pic 4
what I got

ورقة عمل Microsoft Excel جديد ‫(5)‬.xlsx
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-19000
3			SALARY	3000
4			NET AMOUNT	-16000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	4000
9			SALARY	3000
10			NET AMOUNT	7000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-4000
15			SALARY	2500
16			NET AMOUNT	-1500
EMPLOYEES

the right based on op

ورقة عمل Microsoft Excel جديد ‫(5)‬.xlsx
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-17000
3			SALARY	3000
4			NET AMOUNT	-14000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

 

[DanteAmor]

With this on employees sheet

varios 13ene2024b.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-20000
3			SALARY	3000
4			NET AMOUNT	-17000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	-2000
9			SALARY	3000
10			NET AMOUNT	1000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	1000
15			SALARY	2500
16			NET AMOUNT	3500
EMPLOYEES

An this on TT sheet:

varios 13ene2024b.xlsm
	A	B	C	D
1	DATE	NAME	DETAILS	AMOUNT
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2000
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2500
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3000
5	14/03/2023	OMAR ALI OMAR	Pay payment bank	5000
TT

The result is:

varios 13ene2024b.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-17000
3			SALARY	3000
4			NET AMOUNT	-14000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

The results are correct, but check the values in TT sheet.

 

[Omran Y]

here is again testing

ورقة عمل Microsoft Excel جديد ‫(5)‬.xlsx
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-22000
3			SALARY	3000
4			NET AMOUNT	-19000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

Cell Formulas
Range	Formula
D16	D16	=D14+D15

ورقة عمل Microsoft Excel جديد ‫(5)‬.xlsx
	A	B	C	D
1	DATE	NAME	DETAILS	AMOUNT
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2000
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2500
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3000
5	14/03/2023	OMAR ALI OMAR	Pay payment bank	5000
TT

this is what I got

ورقة عمل Microsoft Excel جديد ‫(5)‬.xlsx
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-19000
3			SALARY	3000
4			NET AMOUNT	-16000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	4000
9			SALARY	3000
10			NET AMOUNT	7000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-4000
15			SALARY	2500
16			NET AMOUNT	-1500
EMPLOYEES

the right

ورقة عمل Microsoft Excel جديد ‫(5)‬.xlsx
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-17000
3			SALARY	3000
4			NET AMOUNT	-14000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

I would see your comment.
thanks for your time .

 

[DanteAmor]

Why -17000?

Omar starts with -22000:

On TT sheet:

Then:

if the column C in TT sheet contains Advance payment then should subtract amount

-22000 - 2000 (Advance payment) = -24000

if the column C in TT sheet contains Pay payment then should sum amount

-24000 + 5000 (Pay payment) = -19000

The sums are according to the OP.

​

So what's the problem, is your original specification wrong?

 

[Omran Y]

and when repeat the same name in TT sheet then should move to next row when add new data

it will ignore the old data should calculate based on last data in lastrow

Why -17000?

in this case Omar starts with -22000: and select 5000 from the same name in last row and ignore old data in second row in TT sheet to become -22000+5000=-17000
always should search for the lastrow in TT sheet when repeat the same name .

 

[DanteAmor]

Ok try this:

Sub sum_or_subtract()
  Dim f As Range, c As Range
  Dim det As String
  Dim v As Double
  Dim lr As Long
   
  det = LCase("Advance payment")
  With Sheets("TT")
    lr = .Range("B" & Rows.Count).End(3).Row
    For Each c In .Range("B2:B" & lr)
      If WorksheetFunction.CountIf(.Range(c, .Range("B" & lr)), c.Value) = 1 Then
        Set f = Sheets("EMPLOYEES").Range("C:C").Find(c.Value, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          v = c.Offset(, 2).Value * IIf(Left(LCase(c.Offset(, 1).Value), Len(det)) = det, -1, 1)
          f.Offset(0, 1).Value = f.Offset(0, 1).Value + v
          f.Offset(2, 1).Value = f.Offset(0, 1).Value + f.Offset(1, 1).Value
        End If
      End If
    Next
  End With
End Sub