Solved

Filter on multiple columns

Posted on 2011-09-11
9
302 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)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 42

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 48

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 42

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
 
LVL 48

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 42

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 48

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 42

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 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