Dropdown filter to show year of date and contain text

[mfauzanw]

Hi Expert, I would to show my data by filtering date and text contained.

The date is filtered by the year, and the text is by its contained.

Here i drop the workbook to figured my problem.

Thanks, Expert!

 

[Logit]

Here is a macro approach. Paste into a regular module :

Sub SearchData()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim dataRange As Range
    Dim resultRange As Range
    Dim cell As Range
    Dim matchFound As Boolean
    Dim resultRow As Long
    Dim searchTerm1 As String
    Dim valuesInD() As String
    Dim value As Variant
    Dim searchYear As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust the sheet name as needed
    Set searchRange = ws.Range("G2:I2")
    Set dataRange = ws.Range("B5:D19")
    Set resultRange = ws.Range("G5")
    
    searchTerm1 = searchRange.Cells(1, 3).Value
    searchYear = CInt(searchRange.Cells(1, 2).Value) ' Convert the search term in H2 to an integer
    
    resultRow = resultRange.Row
    
    ' Clear previous search results
    ws.Range(resultRange, ws.Cells(ws.Rows.Count, resultRange.Column)).ClearContents
    
    ' Loop through each row in the data range
    For Each cell In dataRange.Rows
        matchFound = True
        
        ' Check column B for a match
        If ws.Cells(cell.Row, 2).Value <> searchRange.Cells(1, 1).Value Then matchFound = False
        
        ' Check column C for the correct year
        If Year(ws.Cells(cell.Row, 3).Value) <> searchYear Then matchFound = False
        
        ' If columns B and C match, then check column D
        If matchFound Then
            ' Split the values in column D and check each part for a match
            valuesInD = Split(ws.Cells(cell.Row, 4).Value, ",")
            matchFound = False ' Reset matchFound for column D check
            For Each value In valuesInD
                If Trim(value) = searchTerm1 Then
                    matchFound = True
                    Exit For
                End If
            Next value
        End If
        
        ' If a match is found, copy the row to the result range
        If matchFound Then
            ws.Cells(resultRow, 7).Value = ws.Cells(cell.Row, 2).Value
            ws.Cells(resultRow, 8).Value = ws.Cells(cell.Row, 3).Value
            ws.Cells(resultRow, 9).Value = ws.Cells(cell.Row, 4).Value
            resultRow = resultRow + 1
        End If
    Next cell
    
    ' Check if any matches were found
    If resultRow = resultRange.Row Then
        MsgBox "No matches found."
    End If
End Sub

 

[mfauzanw]

Amazing! Such an advance solving. Let me try to understand the macro first.

Is there any solution without a macro?

 

[p45cal]

Another macro approach: A one liner:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3:I3")) Is Nothing Then Range("B4:D19").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("G1:I2"), CopyToRange:=Range("G4:I4"), Unique:=False
End Sub

It uses Advanced Filter and a criteria range G1:I2 (highlighted in green and which I've hidden on the sheet).

 

[p45cal]

Is there any solution without a macro?

Yes, I think you have a version of Excel which supports the worksheet function FILTER, so see cell K5 in the attached.

=FILTER(data,(IF(letterFilter="all",LEN(letters)>-1,letterFilter=letters))*(YEAR(date)=yearfilter)*(IF(fruitFilter="all",LEN(fruit)>-1,ISNUMBER(SEARCH(fruitFilter,fruit)))),"Nothing found")

The likes of len(letters)>-1 was the first way I thought of to get a full column of TRUEs but there'll be a smarter way!

Note that this attached file also contains a correction to the advanced filter method that I put forward in msg#4 (it wasn't seeing the second fruit in column D - I've left it filtered as the previous version would have incorrectly returned no rows).