Advanced Filtering

[seguin85]

Hi All,

I have a sheet with names and other personal information from columns B-M.

I have dates for each day from rows N-JA.

What I want is to be able to set up a filter that will show if a non-blank value shows up in a certain date range.

Example: for June 15-August 1 I want to filter if any row within that date range.
So if employee 1 is scheduled to work on any of those days he/she will be shown.

 

[cerfani]

You mean columns N-JA right? You should reformat your table so you create a column for the dates and list only one day on a row (that means multiple rows for a single person but all are unique because you define date) Then have your data in a column next to the date column. Then use excel autofilter to show blanks on that and/or you can pivot even a list of names or whatever. Excel is efficient in working with standard tables where row 1 is a header and underneath is the data.

Your table is using 2 dimensions to list data and that makes it harder for excel. You cant filter vertically without writing a macro to hide columns.

 

[seguin85]

Oh sorry yes, columns N-JA.

Here is an example of my data:

Em	#	5/12/17	5/13/17	5/14/17	5/15/17	5/16/17	5/17/17	5/18/17
Dave	2	M	M		S		S
John	3
Emily	4			S				S

<tbody>
</tbody>

So in this example I choose dates 5/12/17-5/18/17. I want to see who is scheduled on any of those dates. So only Dave an Emily will be shown.

 

[cerfani]

Your table requires a macro to filter... simply reorganize the data and you can use autofilter. A table should have a header row and that is it. Your header uses data as a header. You are asking to filter by headers and excel cant do that but it can be written in vba. You would loop through columns looking for blanks and if true you hide the column.

Sub FilterBlankColumns()    Dim i, r As Long
    Dim column As Range
    r = Selection.Row
    For i = 14 To 248 'column N-JA
        If IsEmpty(Cells(r, i)) Then
         Set column = Cells(r, i).EntireColumn
         column.Hidden = True
        End If
    Next i
End Sub

edit: this isnt what you wanted i wrote quickly but just noticed you wanted something else... the idea is the same

 

[Marcelo Branco]

Oh sorry yes, columns N-JA.

Here is an example of my data:

Em	#	5/12/17	5/13/17	5/14/17	5/15/17	5/16/17	5/17/17	5/18/17
Dave	2	M	M		S		S
John	3
Emily	4			S				S

<tbody>
</tbody>

So in this example I choose dates 5/12/17-5/18/17. I want to see who is scheduled on any of those dates. So only Dave an Emily will be shown.

Try a formula as criteria. Something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Em​	#​	05/12/2017​	05/13/2017​	05/14/2017​	05/15/2017​	05/16/2017​	05/17/2017​	05/18/2017​
2​	Dave​	2​	M​	M​		S​		S​			TRUE​
3​	John​	3​
4​	Emily​	4​			S​				S​
5​
6​
7​	Start Date​	End Date​
8​	05/12/2017​	05/18/2017​

References dates in A8:B8

Leave K1 blank

Formula in K2
=COUNTIFS($C$1:$I$1,">="&$A$8,$C$1:$I$1,"<="&$B$8,C2:I2,"<>")>0

Then in Advanced Filter select K1:K2 as Criteria Range

Hope this helps

M.

 

[cerfani]

Sub FilterBlankColumns()
    Dim r, c, endC, endR As Long
    endC = Selection.column + Selection.Columns.count - 1
    endR = Cells(Cells.Rows.count, 1).End(xlUp).Row
    For c = Selection.column To endC
        For r = 2 To endR
            If IsEmpty(Cells(r, c)) Then
                Cells(r, c).EntireRow.Hidden = True
            End If
        Next r
    Next c
End Sub

try that

 

[seguin85]

Thanks, I'm going to try the advanced filter first, then if it doesn't get to what I need I will try the macro.

Thanks so far.

 

[cerfani]

after writing the macro I realized it hides when it finds the first blank but it needs to actually be edited so it only hides if it makes it to the end of the range without finding a blank

Sub FilterBlankColumns()
    Dim r, c, endC, endR As Long
    endC = Selection.column + Selection.Columns.count - 1
    endR = Cells(Cells.Rows.count, 1).End(xlUp).Row
    For c = Selection.column To endC
        For r = 2 To endR
            If Not IsEmpty(Cells(r, c)) Then
                GoTo break
            End If
        Next r
        
        Cells(r, c).EntireRow.Hidden = True
break:
    Next c
End Sub

so if the filtering thing doesnt work then i think this code should work (untested)

but I would start formatting your table into a proper table. I deal with clients where they put their data into formats like this and it only ever creates extra work. It isnt even practical for a human to read in this manner. You should have a header and take advantage of simple filtering... the table format causes unnecessary complications.

I dont know what advanced filtering is but if you create a true/false helper column like described then you just have that return true or false if row is blank and you filter by that.

 

[Marcelo Branco]

Thanks, I'm going to try the advanced filter first, then if it doesn't get to what I need I will try the macro.

Thanks so far.

Be careful with the absolute references on the first two pairs, criteria_range, criteria, and relative references in the last criteria_range (C2:I2 in the example above)
If there are formulas in your range (C2:Ix) that, for some reason, return "" in some cases, try this alternative formula (more robust)
=COUNTIFS($C$1:$I$1,">="&$A$8,$C$1:$I$1,"<="&$B$8,C2:I2,"?*")>0

Hope it works.

M.

 

[seguin85]

Thanks, I used the helper column (great idea and very easy to implement). I added a macro and attached it to a button to I don't have to scroll all the way to the right of the page to filter by the helper column.