adapting code to merge amount for duplicates item on usrform

[abdo meghari]

Hi
I would adapt @DanteAmor's code

Option Explicit
Dim a As Variant

Private Sub TextBox1_Change()
  Call FilterData
End Sub



Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim i As Long, j As Long, k As Long
 
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
     Then
      k = k + 1
      For j = 1 To 7
        b(k, j) = a(i, j)
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub

Private Sub UserForm_Activate()
  a = Sheets("purchase").Range("A2:G" & Sheets("purchase").Range("D" & Rows.Count).End(3).Row).Value
End Sub

I would when I write item in txtbox1 then should merge amount in column G for duplicates item.
and change the date column A to auto srial 1,2,3 when merging. but when clear then will return the original data as the original code does it.
thanks

 

[abdo meghari]

here is data

ABDO1.xlsm
	A	B	C	D	E
1	DATE	CLIENT NO	INVOICE NO	BRAND	BALANCE
2	01/05/2021	CUS-BS-1	INV-BS-1	1200R20 G580 JAP	95.00
3	01/06/2021	CUS-BS-1	INV-BS-1	1200R20 G580 THI	10.00
4	01/07/2021	CUS-BS-1	INV-BS-1	1200R20 G580 JAP	5.00
5	01/08/2021	CUS-BS-1	INV-BS-1	1200R20 R187 THI	10.00
6	01/09/2021	CUS-BS-2	INV-BS-2	1200R24 G580 JAP	5.00
7	01/10/2021	CUS-BS-2	INV-BS-2	1200R20 G580 JAP	30.00
8	01/11/2021	CUS-BS-2	INV-BS-2	1200R20 G580 THI	50.00
9	01/12/2021	CUS-BS-2	INV-BS-2	1400R20 VSJ JAP	12.00
10	02/12/2021	CUS-BS-2	INV-BS-2	1400R20 R180 JAP	13.00
purchase

and what I want when search in textbox based on column D

 

[abdo meghari]

is there any chance to do that ?

 

[DanteAmor]

would when I write item in txtbox1 then should merge amount in column G for duplicates item.

Based on your example, it should be column "E".

And when is it duplicated? when the value in column D is repeated or when the values in columns B, C, and D are repeated.
Because if it is when the value of column D is repeated, there is no point in adding the data of columns B and C, because they are different, for example:

In the previous example (key only column D), the mark is the same, the sum is 130, but the customer and invoice data are different, but if it is accumulated in a single record, only one customer and one invoice will appear:

If you use the key from columns 2, 3 and 4:

Try:

Option Explicit
Dim a As Variant

Private Sub TextBox1_Change()
  Call FilterData
End Sub

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim i As Long, j As Long, k As Long, y As Long
  Dim dic As Object
  Dim ky As Variant
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
    
    If UCase(a(i, 4)) Like "*" & UCase(txt1) & "*" Then
      ky = a(i, 4)
     
      If Not dic.exists(ky) Then
        k = k + 1
        y = y + 1
        dic(ky) = y
      End If
      
      y = dic(ky)
      
      b(y, 1) = y
      b(y, 2) = a(i, 2)
      b(y, 3) = a(i, 3)
      b(y, 4) = a(i, 4)
      b(y, 5) = b(y, 5) + a(i, 5)
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub

Private Sub UserForm_Activate()
  a = Sheets("purchase").Range("A2:G" & Sheets("purchase").Range("D" & Rows.Count).End(3).Row).Value
End Sub

If you want the extended key, change this line:
ky = a(i, 4)

By this:
ky = a(i, 2) & "|" & a(i, 3) & "|" & a(i, 4)


Regards
Dante Amor

 

[abdo meghari]

Hi Dante again ,
sorry about confusion and post some errors in details
both two suggestions works perfectly .
many thanks buddy.