Solved

Filter on multiple columns

Posted on 2011-09-11
9
268 Views
Last Modified: 2012-05-12
I have a spreadsheet that contains several thousand rows.

Each row contains a Group (Col C), Sub-Group (Col D), and numerous columns (headers represent calendar months) which contain receipts and expenditures in those Group/Sub-Group categories for each month.

Using VBA, I need to filter this worksheet on specific values of Group (=100) and sub-Group (=1) and then exclude records where all of the month columns (columns E - I) are equal to 0.

I've attached a workbook with sample data.
ComplexFilter.xlsm
0
Comment
Question by:Dale Fye (Access MVP)
  • 4
  • 3
9 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 36519877
While you can do this with advanced filter, controlling criteria on the spreadsheet, you've explicitly asked for VBA, so the app i write clears any existing filters on the worksheet, defines the db range for filtering, turns on the filter, then sets the filter for Group, subGroup, and, in a loop, the month columns.

I also added a simple macro to turn the filter off, as needed.

Both can be accessed thru buttons on the primary worksheet.

Here's the code:
 
Sub filterMacro()
Dim wkb As Workbook
Dim sht As Worksheet
Dim dbRange As Range
Dim dbStartCell As Range
Dim grpCol As Long
Dim subGrpCol As Long
Dim mnthCol As Long

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
    Set dbStartCell = sht.Range("A4")
    
    Set dbRange = sht.Range(dbStartCell, sht.Range("A" & Rows.Count).End(xlUp))
    Set dbRange = sht.Range(dbRange, sht.Cells(dbStartCell.Row, Columns.Count).End(xlToLeft))
    
    grpCol = 3
    subGrpCol = 4
    
    dbRange.AutoFilter field:=grpCol, Criteria1:="100"
    dbRange.AutoFilter field:=subGrpCol, Criteria1:="1"
    
    For mnthCol = sht.Range("E1").Column To sht.Range("I1").Column
        dbRange.AutoFilter field:=mnthCol, Criteria1:="-"
    Next mnthCol
    
End Sub
Sub filterOff()
Dim wkb As Workbook
Dim sht As Worksheet

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
End Sub

Open in new window


See attached workbook.

PS - note, wkb points to ThisWorkbook - that's the workbook where this code resides.  If you want to rather run the app in this workbook on another workbook, just change the statement:

set wkb = ThisWorkbook

to

set wkb = ActiveWorkbook

Cheers,

Dave
ComplexFilter-r1.xlsm
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 350 total points
ID: 36519904
Another approach:

Sub MakeFilter()
    
    Dim Counter As Long
    Dim LastC As Long
    Dim LastR As Long
    
    Const GroupValue As Long = 100
    Const Subgroup As Long = 1
    Const MonthAmt As Long = 0
    Const FirstMonthCol As Long = 5
    Const LastMonthCol As Long = 9
    
    With ThisWorkbook.Worksheets("Trial Balance")
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        LastC = .Cells(4, .Columns.Count).End(xlToLeft).Column + 1
        .Cells(4, LastC) = "Check"
        .Range(.Cells(5, LastC), .Cells(LastR, LastC)).Formula = "=SUM(" & _
            .Cells(5, FirstMonthCol).Address(False, False) & ":" & _
            .Cells(5, LastMonthCol).Address(False, False) & ")"
        .[a4].AutoFilter
        .[a4].AutoFilter Field:=3, Criteria1:=GroupValue, Operator:=xlAnd
        .[a4].AutoFilter Field:=4, Criteria1:=Subgroup, Operator:=xlAnd
        .[a4].AutoFilter Field:=LastC, Criteria1:="<>" & MonthAmt, Operator:=xlAnd
    End With
    
End Sub

Open in new window

0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 36522489
Turns out I was premature in my selection of a solution.  I failed to realize Dave's solution was filtering on "=0" in each of the monthly columns.  This results in getting only those rows which contain all zeros.  What I needed was those rows that contain any non-zero value, and I cannot figure out how to do that using Dave's solution.

Although I would prefer not to add a column to the spreadsheet, as Patrick has done, I cannot figure out any other way to do this.  I've replaced the SUM() function with a CountIF() function and am now testing for that column <> 0.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36523825
Ah, I misread.  Only need to change the criteria to >0 as opposed to =0

That would be changing this line:

        dbRange.AutoFilter field:=mnthCol, Criteria1:="<>0"

See attached,

Dave
ComplexFilter-r2.xlsm
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 36523991
Dave,

What your original code gave me was:

"E = 0 and F = 0 and G = 0 and H = 0 and I = 0"

Your new recommendation ("<> 0") gets me:

"E <> 0 and F <> 0 and G <> 0 and H <> 0 and I <> 0"

when what I really want is:

 "E <> 0 or F <> 0 or G <> 0 or H <> 0 or I <> 0"

What I'm using now, which is working quite well is a formula in the L column, which is hidden to the users.  It looks like:

=AND(IF($C5=L$2, TRUE, FALSE), IF($D5=L$3, TRUE, FALSE), IF(COUNTIF($E5:$I5,"<> 0") > 0, TRUE, FALSE))

With this, I just push the value of the Group to "L2" and of the sub-group to "L3".

Then I filter the sheet on the value in column L = true


0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 150 total points
ID: 36524314
Ahh -  sorry, I'm having a Monday morning without enough coffee!

Thanks for the clarification...

So E <>0 or F <> 0 or G <> 0 or H <> 0 or I <> 0 is exactly the same as NOT (E=0 and F=0 and G=0 and H=0 and I=0).  That's a bit hard to do without a helper column, as matthewspatrick provided - however, it is also the same as SUM(E:I)>0.

The attached provides that solution, without a helper, though I'm starting to like matthewspatrick's solution, more, as its easier to implement, though I understand not wanting to have that column (could delete it after?)

At any rate, here's my revised code:
Sub filterMacro()
Dim wkb As Workbook
Dim sht As Worksheet
Dim dbRange As Range
Dim dbStartCell As Range
Dim grpCol As Long
Dim subGrpCol As Long
Dim mnthCol As Long
Dim sumRow As Long
Dim sumThis As Range, mySum As Double
Dim dbNoHeader As Range

    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
    Set dbStartCell = sht.Range("A4")
    
    Set dbRange = sht.Range(dbStartCell, sht.Range("A" & Rows.Count).End(xlUp))
    Set dbRange = sht.Range(dbRange, sht.Cells(dbStartCell.Row, Columns.Count).End(xlToLeft))
    
    Set dbNoHeader = dbRange.Offset(1, 4).Resize(dbRange.Rows.Count - 1, 5) ' just the data, for hiding rows
    
    grpCol = 3
    subGrpCol = 4
    
    dbRange.AutoFilter field:=grpCol, Criteria1:="100"
    dbRange.AutoFilter field:=subGrpCol, Criteria1:="1"
    
    'now determine if rows should be hidden based on critiera
    For sumRow = 1 To dbNoHeader.Rows.Count
        Set sumThis = sht.Range(dbNoHeader.Cells(sumRow, 1), dbNoHeader.Cells(sumRow, dbNoHeader.Columns.Count))
        Debug.Print sumThis.Address, Application.WorksheetFunction.Sum(sumThis)
        mySum = Application.WorksheetFunction.Sum(sumThis)
        If mySum = 0 Then
            sht.Cells(sumRow + dbNoHeader.Cells(1, 1).Row - 1, 1).EntireRow.Hidden = True
        End If
    Next sumRow
    
    Application.ScreenUpdating = True
End Sub
Sub filterOff()
Dim wkb As Workbook
Dim sht As Worksheet

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
End Sub

Open in new window


See attached,

Dave
ComplexFilter-r3.xlsm
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 36524424
Dave,

Your assessment that what I'm looking for (logically) is the same as:

"NOT (E=0 and F=0 and G=0 and H=0 and I=0). "

is correct.

I like the technique of using EntireRow.Hidden and will keep that in my bag of tricks.

But I'm comfortable with what I have working now.  Thanks for the help.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36524574
I must post one more time.  I finally understand what you mean.

If Countif(sumrange,"<>0") then don't hide it.

Here's the solution.

Cheers,

Dave
ComplexFilter-r4.xlsm
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now