summing amount over previously selected cells based on one cell for more than name

[Alaa mg]

Hello
I need sum amounts for columns D:F based on matching names in column C
so in cell H2 will select the name and should match with column C , then should sum amounts for all of names in I2:K2 after select the last name in H2
but every time I will select name in H1 then should sum over previous name is ever selected and when clear H2 then will I2:K2 is empty and start from the beginning
to understand more

CS.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE		NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6,000.00	7,000.00	-1,000.00
3	2	OPENING BALANCE 27/07/2023	MLA	6,000.00	10,000.00	-4,000.00
4	3	OPENING BALANCE 27/07/2023	ALAA	4,000.00	1,000.00	3,000.00
5	4	OPENING BALANCE 27/07/2023	MAAL	2,000.00	1,200.00	800.00
6	TOTAL			18,000.00	19,200.00	-1,200.00
CS

Cells with Data Validation
Cell	Allow	Criteria
H2:J2	List	=$C$2:$C$5

when i select name(LLA) from H1 will populate amounts I2:K2

CS.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE		NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6,000.00	7,000.00	-1,000.00		LLA	6,000.00	7,000.00	-1,000.00
3	2	OPENING BALANCE 27/07/2023	MLA	6,000.00	10,000.00	-4,000.00
4	3	OPENING BALANCE 27/07/2023	ALAA	4,000.00	1,000.00	3,000.00
5	4	OPENING BALANCE 27/07/2023	MAAL	2,000.00	1,200.00	800.00
6	TOTAL			18,000.00	19,200.00	-1,200.00
CS

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=$C$2:$C$5

when select another name(MLA) should sum over amounts for name is ever selected ( should merge amounts for names(LLA,MLA))
to become like this

CS.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE		NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6,000.00	7,000.00	-1,000.00		MLA	12,000.00	17,000.00	-5,000.00
3	2	OPENING BALANCE 27/07/2023	MLA	6,000.00	10,000.00	-4,000.00
4	3	OPENING BALANCE 27/07/2023	ALAA	4,000.00	1,000.00	3,000.00
5	4	OPENING BALANCE 27/07/2023	MAAL	2,000.00	1,200.00	800.00
6	TOTAL			18,000.00	19,200.00	-1,200.00
CS

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=$C$2:$C$5

and if I select name(MAAL) then will merge amounts for names(LLA,MLA,MAAL) to become like this

CS.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE		NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6,000.00	7,000.00	-1,000.00		MAAL	14,000.00	18,200.00	-4,200.00
3	2	OPENING BALANCE 27/07/2023	MLA	6,000.00	10,000.00	-4,000.00
4	3	OPENING BALANCE 27/07/2023	ALAA	4,000.00	1,000.00	3,000.00
5	4	OPENING BALANCE 27/07/2023	MAAL	2,000.00	1,200.00	800.00
6	TOTAL			18,000.00	19,200.00	-1,200.00
CS

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=$C$2:$C$5

and when clear H1 then should clear I2:K2 and start from the beginning
notice: if I repeat selection the name has already selected should not repeat merge (just merge one time not more for the same name)

 

[Peter_SSs]

Try this Worksheet_Change event code. If unsure how to implement it post back

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rfound As Range
  
  If Not Intersect(Target, Range("H2")) Is Nothing Then
    If Len(Range("H2").Value) = 0 Then
      Range("I2:K2").ClearContents
    Else
      Set rfound = Columns("C").Find(What:=Range("H2").Value, LookAt:=xlWhole)
      If Not rfound Is Nothing Then
        rfound.Offset(, 1).Resize(, 3).Copy
        Range("I2").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
        Application.CutCopyMode = False
      End If
    End If
  End If
End Sub

 

[Alaa mg]

seem to work perfectly without problem
many appreciated for your help .

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.