Filter data in listbox on form based on matching part of item in two comboboxes

[Ali M]

Hello
I search for codes in this forum but I don't find what I want .
so I have many sheets contains items in column C,D theses standers to match with combobox1,2

pp.xlsm
	A	B	C	D	E
1	DATE	CLIENT NAME	INFORMATION	TYP OF PAYING	TOTAL
2	01/01/2024	ALI	SALES INVOICE NO SAL2333	CASH IN NO CS2000	10,000.00
3	01/01/2024	ALI	SALES INVOICE NO SAL2333	CASH IN NO CS2000	10,000.00
4	02/01/2024	ALI	SALES INVOICE NO SAL2334	BANK IN NO BC200223	20,000.00
5	02/01/2024	ALI	SALES INVOICE NO SAL2334	BANK IN NO BC200223	20,000.00
6	05/01/2024	OMAR	BUYING INVOICE NO BU23323	BANK OUT NO BC200224	30,000.00
7	05/01/2024	OMAR	BUYING INVOICE NO BU23323	BANK OUT NO BC200224	30,000.00
8	05/01/2024	OMAR	BUYING INVOICE NO BU23323	BANK OUT NO BC200224	30,000.00
9	08/01/2024	ALI	SALES INVOICE NO SAL2335	DEFERRED PAYMENT NO BDF2000	15,000.00
10	09/01/2024	OMAR	BUYING INVOICE NO BU23324	PUT OFF PAYMENT PFP NO R7000	10,000.00
11	09/01/2024	OMAR	BUYING INVOICE NO BU23324	PUT OFF PAYMENT PFP NO R7000	10,000.00
12	11/01/2024	OMAIR	SALES RETURNS INVOICE NO ST21233	BANK OUT NO BC2002244	10,000.00
13	11/01/2024	OMAIR	SALES RETURNS INVOICE NO ST21233	BANK OUT NO BC2002244	10,000.00
14	13/01/2024	OMAR	BUYING RETURNS INVOICE NO SP23366	BANK IN NO BC20022554	30,000.00
15	13/01/2024	OMAR	BUYING RETURNS INVOICE NO SP23366	BANK IN NO BC20022554	30,000.00
16	13/01/2024	OMAR	BUYING RETURNS INVOICE NO SP23366	BANK IN NO BC20022554	30,000.00
17	14/01/2024	OMAR	BUYING INVOICE NO BU23325	CASH OUT NO BC200225	2,000.00
18	14/01/2024	OMAR	BUYING INVOICE NO BU23325	CASH OUT NO BC200225	2,000.00
19	13/01/2024	OMAR	BUYING RETURNS INVOICE NO SP23367	CASH IN NO CS200223222	30,000.00
SDR

pp.xlsm
	A	B	C	D	E
1	DATE	CLIENT NAME	INFORMATION	TYP OF PAYING	TOTAL
2	11/01/2024	ALI	BUYING INVOICE NO BU23444	PUT OFF PAYMENT PFP NO R7001	7,000.00
3	11/01/2024	ALI	BUYING INVOICE NO BU23444	PUT OFF PAYMENT PFP NO R7001	6,000.00
4	11/01/2024	ALI	BUYING INVOICE NO BU23444	PUT OFF PAYMENT PFP NO R7001	20,000.00
5	11/01/2024	ALI	BUYING INVOICE NO BU23444	PUT OFF PAYMENT PFP NO R7001	20,000.00
6	12/01/2024	OMAR	SALES INVOICE NO SAL2334565	DEFERRED PAYMENT NO BDF2001	3,000.00
7	12/01/2024	OMAR	SALES INVOICE NO SAL2334565	DEFERRED PAYMENT NO BDF2001	2,000.00
8	12/01/2024	OMAR	SALES INVOICE NO SAL2334565	DEFERRED PAYMENT NO BDF2001	2,300.00
9	12/01/2024	OMAR	SALES INVOICE NO SAL2334565	DEFERRED PAYMENT NO BDF2001	1,200.00
10	12/01/2024	OMAR	SALES INVOICE NO SAL2334565	DEFERRED PAYMENT NO BDF2001	1,400.00
11	13/01/2024	OMAR	SALES RETURNS INVOICE NO ST2123113	BANK OUT NO BC200224411	1,000.00
12	13/01/2024	OMAR	SALES RETURNS INVOICE NO ST2123113	BANK OUT NO BC200224411	1,200.00
13	13/01/2024	OMAR	SALES RETURNS INVOICE NO ST2123113	BANK OUT NO BC200224411	400.00
14	13/01/2024	OMAR	SALES RETURNS INVOICE NO ST2123113	CASH OUT NO CS2002244111	30,000.00
FMR

pp.xlsm
	A	B	C	D	E
1	DATE	CLIENT NAME	INFORMATION	TYP OF PAYING	TOTAL
2	11/01/2024	OMRAN	BUYING INVOICE NO BU2332411	PUT OFF PAYMENT PFP NO R70011	10,000.00
3	11/01/2024	OMRAN	BUYING INVOICE NO BU2332411	PUT OFF PAYMENT PFP NO R70011	10,000.00
4	12/01/2024	OMRAN	BUYING LOW INVOICE NO LU2332411	BANK IN NO BC200223111	1,200.00
5	12/01/2024	OMRAN	BUYING LOW INVOICE NO LU2332411	BANK IN NO BC200223111	8,000.00
6	12/01/2024	OMRAN	BUYING LOW INVOICE NO LU2332411	BANK IN NO BC200223111	900.00
7	13/01/2024	OMRAN	EXPENSES INVOICE NO EX23323	BANK OUT NO BBC2002241	12,000.00
8	13/01/2024	OMAR	EXPENSES INVOICE NO EX23324	BANK OUT NO BBC2002242	2,000.00
9	14/01/2024	ALI	SALES LOW INVOICE NO LAL233511	BANK OUT NO BSABC200224311	15,000.00
10	14/01/2024	OMAR	SALES RETURNS INVOICE NO ST1221233	DEFERRED PAYMENT NO BDF200110	10,000.00
11	14/01/2024	OMAIR	SALES RETURNS INVOICE NO ST1221233	DEFERRED PAYMENT NO BDF200110	10,000.00
12	14/01/2024	OMAIR	SALES RETURNS INVOICE NO ST1221233	DEFERRED PAYMENT NO BDF200110	10,000.00
13	14/01/2024	ALI	SALES LOW INVOICE NO LAL233511	CASH OUT NO CS203457800	15,000.00
14	15/01/2024	OMAR	SALES INVOICE NO SAL2334565	CASH IN NO CS600001	1,400.00
BR

combobox1,2 as show

I would filter data based on many cases
the part of item in combobox1 will match with column C, part of item in combobox2 will match with column D and textbox1,textbox2 will match dates within column A
so here is cases
case1

CASE2

CASE3

CASE4

CASE5

CASE6

should populate data in list box across sheets and insert TOTAL ro in listbox to sum whole column contains amount.
I will add new items for each sheet , data about 6500 rows for each sheet.
thanks

 

[DanteAmor]

I would filter data based on many cases
the part of item in combobox1 will match with column C

The items BUYING and SALES are ambiguous, since in SALES it is in "SALES RETURNS", "SALES INVOICE" and it is also in "SALES LOW", that means that when you choose "SALES" it will show you those 3 concepts, which includes "SALES LOW". For you to consider.
Note: If you need "SALES LOW" not to appear when you select "SALES", you should have commented on it in your OP. So you'll have to clarify it in another thread.

Having said the above.

I ran tests with 7500 records on each sheet.
Each query takes less than a second to complete.

Try the following code in your userform:

Option Explicit

Dim a As Variant

Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant, txt1 As String, txt2 As String
  Dim i As Long, j As Long, k As Long
  Dim b As Variant, c As Variant
  Dim tot As Double
  
  ListBox1.Clear
  tot = 0
  ReDim b(1 To UBound(a, 1) + 1, 1 To 5)
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb1 = a(i, 3) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 4) Else cmb2 = ComboBox2.Value
    
    If TextBox1.Value <> "" And TextBox2.Value <> "" Then
      txt1 = checkDate(TextBox1.Value, a(i, 1))
      txt2 = checkDate(TextBox2.Value, a(i, 1))
    Else
      txt1 = a(i, 1)
      txt2 = a(i, 1)
    End If
    
    If a(i, 3) Like cmb1 & "*" And a(i, 4) Like cmb2 & "*" And _
       a(i, 1) >= CDate(txt1) And a(i, 1) <= CDate(txt2) Then
      k = k + 1
      b(k, 1) = Format(a(i, 1), "dd/mm/yyyy")
      b(k, 2) = a(i, 2)
      b(k, 3) = a(i, 3)
      b(k, 4) = a(i, 4)
      b(k, 5) = Format(a(i, 5), "#,##0.00;-#,##0.00;")
      tot = tot + a(i, 5)
    End If
  Next
  k = k + 1
  b(k, 1) = "TOTAL"
  b(k, 5) = Format(tot, "#,##0.00;-#,##0.00;")
  
  ReDim c(1 To k, 1 To 5)
  For i = 1 To k
    For j = 1 To 5
      c(i, j) = b(i, j)
    Next
  Next
  ListBox1.List = c
End Sub

Function checkDate(valText, fec)
  If valText = "" Or Not IsDate(valText) Or Len(valText) <> 10 Or _
     Mid(valText, 3, 1) <> "/" Or Mid(valText, 6, 1) <> "/" Then
    checkDate = fec
  Else
    checkDate = valText
  End If
End Function

Private Sub ComboBox1_Change()
  If ComboBox1.ListIndex = -1 Then Exit Sub
  Call FilterData
End Sub

Private Sub ComboBox2_Change()
  If ComboBox2.ListIndex = -1 Then Exit Sub
  Call FilterData
End Sub

Private Sub TextBox1_Change()
  With TextBox1
    If .Value = "" Or Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub TextBox2_Change()
  With TextBox2
    If .Value = "" Or Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim itm As Variant, b As Variant, arr As Variant
  Dim lr As Long, i As Long, j As Long, k As Long
  arr = Array("SDR", "FMR", "BR")
  For Each itm In arr
    lr = lr + Sheets(itm).Range("A" & Rows.Count).End(3).Row - 1
  Next
  ReDim a(1 To lr, 1 To 5)
  
  For Each itm In arr
    b = Sheets(itm).Range("A2", Sheets(itm).Range("E" & Rows.Count).End(3)).Value
    For i = 1 To UBound(b, 1)
      k = k + 1
      For j = 1 To UBound(b, 2)
        a(k, j) = b(i, j)
      Next
    Next
  Next
  
  With ListBox1
    .RowSource = ""
    .ColumnCount = 5
  End With
  
  Call FilterData
End Sub

 

[Ali M]

Thanks Dante for the code and your time to write this code for me.

The items BUYING and SALES are ambiguous, since in SALES it is in "SALES RETURNS", "SALES INVOICE" and it is also in "SALES LOW", that means that when you choose "SALES" it will show you those 3 concepts, which includes "SALES LOW". For you to consider.

I expect to say that .

I'm not sure if the excel can do that as I posted . in my mind match whole items in combobox with columns without effect after matching when repeat the same part of the item is existed with the others.

example: SALES RETURNS will just show data for these words without show data for items contains SALES : when show data for SALES RETURNS it must search for two words together ignore every individual word
when search for SALES word then will show based on this word because contains one part
SALES RETURNS : will show based on theses words together ignoring data for SALES ,SALES LOW
SALES LOW :will show based on theses words together ignoring data for SALES ,SALES RETURNS.
so if you have any simple idea to do that I accept that.

 

[DanteAmor]

Thanks Dante for the code and your time to write this code for me

Did you try the code?

In the code You have individual filters by combo, combined filters of combos, individual filters of dates and combined filters of combos and dates. And it works for more than 6000 records on 3 sheets.
That's what you asked for.

What you asked for was this:

the part of item in combobox1 will match with column C

If you select "SALES"
Obviously SALES, SALES low and SALES return, contain the word SALES.

What you didn't ask for is if you select SALES, exclude records containing "SALES LOW" and also exclude "SALES RETURN". Can I explain?

Since you did not request it in your OP that is why it is not considered.

I would like you to review the code with what you did ask for.
If it doesn't work for what you asked for, then you just throw it away.

But if it works for what you asked for, I would like you to comment on it.

And as I already told you, for what you didn't ask for, create a new thread and explain in detail what you want to include and what you want to exclude.

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Ali M]

Did you try the code?

Surely yes.

That's what you asked for.

based on pictures of course not !
see the picture 3 for userform when I select SALES
it doesn't contain SALES RETURNS ,SALES LOW
you didn't ask me why SALES RETURNS ,SALES LOW don't show in the picture 3 for the form !

What you asked for was this:

Ali M said:
the part of item in combobox1 will match with column C

what I meant the item in combobox match with part of item in column C because of contains items after target item for matching .

What you didn't ask for is if you select SALES, exclude records containing "SALES LOW" and also exclude "SALES RETURN". Can I explain?

when select SALES LOW

when select SALES RETURNS

the same thing for BUYING,BUYING RETURNS,BUYING LOW

 

[DanteAmor]

Surely yes.

Any comment about that. Or you're just going to judge my work by what you don't have and not by what you do have.

you didn't ask me why SALES RETURNS ,SALES LOW don't show in the picture 3 for the form !

I don't think it's my fault. I'm trying to help you. If you don't see it that way, then you can throw my effort away.

I suggest that you be more specific when you have an ambiguity in your information.

 

[Ali M]

Any comment about that

I Answered you .

based on pictures of course not !

Or you're just going to judge my work by what you don't have and not by what you do have.

no this is not like that as you think .

I don't think it's my fault

I don't blame you for that, sorry if you think that !

I'm trying to help you.

always you're welcome.

If you don't see it that way, then you can throw my effort away

it's really close !

I suggest that you be more specific when you have an ambiguity in your information.

I thought the pictures reflect my requirements , sorry!

 

[DanteAmor]

Option Explicit

Dim a As Variant

Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant, txt1 As String, txt2 As String
  Dim i As Long, j As Long, k As Long, m As Long, LItm As Long
  Dim b As Variant, c As Variant, itm As Variant
  Dim tot As Double
  Dim bExclude As Boolean
  
  ListBox1.Clear
  tot = 0
  ReDim b(1 To UBound(a, 1) + 1, 1 To 5)
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb1 = a(i, 3) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 4) Else cmb2 = ComboBox2.Value
    
    If TextBox1.Value <> "" And TextBox2.Value <> "" Then
      txt1 = checkDate(TextBox1.Value, a(i, 1))
      txt2 = checkDate(TextBox2.Value, a(i, 1))
    Else
      txt1 = a(i, 1)
      txt2 = a(i, 1)
    End If
    
    If a(i, 3) Like cmb1 & "*" And a(i, 4) Like cmb2 & "*" And _
       a(i, 1) >= CDate(txt1) And a(i, 1) <= CDate(txt2) Then
      
      bExclude = False
      For m = 0 To ComboBox1.ListCount - 1
        If m <> ComboBox1.ListIndex And ComboBox1.ListIndex <> -1 Then
          itm = Split(a(i, 3), " ")(0) & " " & Split(a(i, 3), " ")(1)
          If itm = ComboBox1.List(m) Then
              bExclude = True
              Exit For
          End If
        End If
      Next
      
      If bExclude = False Then
        k = k + 1
        b(k, 1) = Format(a(i, 1), "dd/mm/yyyy")
        b(k, 2) = a(i, 2)
        b(k, 3) = a(i, 3)
        b(k, 4) = a(i, 4)
        b(k, 5) = Format(a(i, 5), "#,##0.00;-#,##0.00;")
        tot = tot + a(i, 5)
      End If
    End If
  Next
  k = k + 1
  b(k, 1) = "TOTAL"
  b(k, 5) = Format(tot, "#,##0.00;-#,##0.00;")
  
  ReDim c(1 To k, 1 To 5)
  For i = 1 To k
    For j = 1 To 5
      c(i, j) = b(i, j)
    Next
  Next
  ListBox1.List = c
End Sub

Function checkDate(valText, fec)
  If valText = "" Or Not IsDate(valText) Or Len(valText) <> 10 Or _
     Mid(valText, 3, 1) <> "/" Or Mid(valText, 6, 1) <> "/" Then
    checkDate = fec
  Else
    checkDate = valText
  End If
End Function

Private Sub ComboBox1_Change()
  If ComboBox1.ListIndex = -1 Then Exit Sub
  Call FilterData
End Sub

Private Sub ComboBox2_Change()
  If ComboBox2.ListIndex = -1 Then Exit Sub
  Call FilterData
End Sub

Private Sub TextBox1_Change()
  With TextBox1
    If .Value = "" Or Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub TextBox2_Change()
  With TextBox2
    If .Value = "" Or Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim itm As Variant, b As Variant, arr As Variant
  Dim lr As Long, i As Long, j As Long, k As Long
  arr = Array("SDR", "FMR", "BR")
  For Each itm In arr
    lr = lr + Sheets(itm).Range("A" & Rows.Count).End(3).Row - 1
  Next
  ReDim a(1 To lr, 1 To 5)
  
  For Each itm In arr
    b = Sheets(itm).Range("A2", Sheets(itm).Range("E" & Rows.Count).End(3)).Value
    For i = 1 To UBound(b, 1)
      k = k + 1
      For j = 1 To UBound(b, 2)
        a(k, j) = b(i, j)
      Next
    Next
  Next
  
  With ListBox1
    .RowSource = ""
    .ColumnCount = 5
  End With
  
  Call FilterData
End Sub

 

[DanteAmor]

Option Explicit

Dim a As Variant

Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant, txt1 As String, txt2 As String
  Dim i As Long, j As Long, k As Long, m As Long, LItm As Long
  Dim b As Variant, c As Variant, itm As Variant
  Dim tot As Double
  Dim bExclude As Boolean
  
  ListBox1.Clear
  tot = 0
  ReDim b(1 To UBound(a, 1) + 1, 1 To 5)
  For i = 1 To UBound(a)
    If ComboBox1.Value = "" Then cmb1 = a(i, 3) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 4) Else cmb2 = ComboBox2.Value
    
    If TextBox1.Value <> "" And TextBox2.Value <> "" Then
      txt1 = checkDate(TextBox1.Value, a(i, 1))
      txt2 = checkDate(TextBox2.Value, a(i, 1))
    Else
      txt1 = a(i, 1)
      txt2 = a(i, 1)
    End If
    
    If a(i, 3) Like cmb1 & "*" And a(i, 4) Like cmb2 & "*" And _
       a(i, 1) >= CDate(txt1) And a(i, 1) <= CDate(txt2) Then
      
      bExclude = False
      For m = 0 To ComboBox1.ListCount - 1
        If m <> ComboBox1.ListIndex And ComboBox1.ListIndex <> -1 Then
          itm = Split(a(i, 3), " ")(0) & " " & Split(a(i, 3), " ")(1)
          If itm = ComboBox1.List(m) Then
              bExclude = True
              Exit For
          End If
        End If
      Next
      
      If bExclude = False Then
        k = k + 1
        b(k, 1) = Format(a(i, 1), "dd/mm/yyyy")
        b(k, 2) = a(i, 2)
        b(k, 3) = a(i, 3)
        b(k, 4) = a(i, 4)
        b(k, 5) = Format(a(i, 5), "#,##0.00;-#,##0.00;")
        tot = tot + a(i, 5)
      End If
    End If
  Next
  k = k + 1
  b(k, 1) = "TOTAL"
  b(k, 5) = Format(tot, "#,##0.00;-#,##0.00;")
  
  ReDim c(1 To k, 1 To 5)
  For i = 1 To k
    For j = 1 To 5
      c(i, j) = b(i, j)
    Next
  Next
  ListBox1.List = c
End Sub

Function checkDate(valText, fec)
  If valText = "" Or Not IsDate(valText) Or Len(valText) <> 10 Or _
     Mid(valText, 3, 1) <> "/" Or Mid(valText, 6, 1) <> "/" Then
    checkDate = fec
  Else
    checkDate = valText
  End If
End Function

Private Sub ComboBox1_Change()
'  If ComboBox1.ListIndex = -1 Then Exit Sub
  Call FilterData
End Sub

Private Sub ComboBox2_Change()
'  If ComboBox2.ListIndex = -1 Then Exit Sub
  Call FilterData
End Sub

Private Sub TextBox1_Change()
  With TextBox1
    If .Value = "" Or Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub TextBox2_Change()
  With TextBox2
    If .Value = "" Or Not IsDate(.Value) Or Len(.Value) <> 10 Or _
     Mid(.Value, 3, 1) <> "/" Or Mid(.Value, 6, 1) <> "/" Then
    Else
      If TextBox2.Value <> "" Then
        Call FilterData
      End If
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim itm As Variant, b As Variant, arr As Variant
  Dim lr As Long, i As Long, j As Long, k As Long
  arr = Array("SDR", "FMR", "BR")
  For Each itm In arr
    lr = lr + Sheets(itm).Range("A" & Rows.Count).End(3).Row - 1
  Next
  ReDim a(1 To lr, 1 To 5)
  
  For Each itm In arr
    b = Sheets(itm).Range("A2", Sheets(itm).Range("E" & Rows.Count).End(3)).Value
    For i = 1 To UBound(b, 1)
      k = k + 1
      For j = 1 To UBound(b, 2)
        a(k, j) = b(i, j)
      Next
    Next
  Next
  
  With ListBox1
    .RowSource = ""
    .ColumnCount = 5
  End With
  
  Call FilterData
End Sub

 

[Ali M]

Awesome !
I will choose code in post #9 this is better when clear combobox .
thank you so much.