Data Filtering – Help needed with some nervous Excel users!

[ellison]

Hi, I’ve noticed that some of my colleagues seem to feel really uneasy (aka downright nervous!) about using a data filter.

No matter how many times they see (& often agree!) how easy it is to set up and use a data filter using either the Excel options in the data “sort & filter or by using keyboard shortcuts like A+F+F, they won’t use it.

And they’re especially worried about applying a filter to the wrong info… (It may be worth pointing out that the only info they ever need to filter is always contained in Column C).

BUT progress (of sorts!), they’re now seeing the advantages of using a filter – and some of them have actually asked if it’s possible to set up something like the below… Sadly for them, I haven’t a clue how to set something like this up, or even if it’s possible!

Option A: Plum perfect solution, 10/10, something like:

In the same way as there are drop down boxes on the Excel options for example like the ones on the type of font and size of font

Instead of that, would it be possible in the Excel options to include 2 drop down boxes and an icon where:

• drop down box-1: to be able to copy and paste a search string to filter the field for (which is only ever the info in Column C).
  • Is that even called a drop down box?!
• Drop down box-2: choose from the 2 options where it’s either “field C contains” or “field C equals”
• an icon = “clear filter button” next to those 2 drop down boxes (rather than having to dig around and find where it is in Excel).

Option B: a mediocre solution, 7 / 10: keyboard shortcuts or maybe icons to give the same 3 options as above? (some would prefer icons, some would prefer keyboard shortcuts, grr).

Option C: awful, 0/ 10: you (as in me!) keep showing them how to set and use the data filter. Oops, received & understood, hence my message on here to try to help them

For the record, I’m pretty sure that once they have a way of making sure that they are getting the data filter on the right field (Column C) , with an easy way of choosing “contains” or “equals” & a really easy way to remove the filter, then they’ll get used to working with it.

Any help / pointers much appreciated.

I should probably add that the files they work on are pretty big i.e. 10-20 meg with about 15 fields and about 20K-ish rows.

If you needed any more details, please let me know

Huge thanks to anybody for taking a peek at this

 

[ellison]

Hi, just reporting back in on progress so far...

The code for an advanced filter where:
- data is kept in the same place (not copied into another location)
- sample range is A1 to M10, header on 1st row
- the criteria is from a cell address (as opposed to an actual value) ie cell D1
- WIP trying to figure out how to change the criteria from "equals" to "partial match"

Sub ColumnDContains()
'
' ColumnDContains
'

'
Range("D1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$M$10").AutoFilter Field:=4, Criteria1:=Range("D1"), _
Operator:=xlAnd
End Sub


**Next Step 1:
- make the criteria to be "contains" as opposed to equals (i.e. partial match)
- I think I need to change Criteria1:=Range("D1"), but currently stuck.

Next Step 2, Cancelling the auto filter, done:

Sub CancelFilterA()
'
' CancelFilterA Macro
' CancelFilterA to remove the filter from Col D
'

'
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveSheet.ShowAllData
End Sub

Next step 3, assigning macros to buttons, done (using the macro recorder)


Thanks for any help you can provide, especially on how to change this line to being a partial match:

ActiveSheet.Range("$A$1:$M$10").AutoFilter Field:=4, Criteria1:=Range("D1"), _



For reference, sample data with filter:

Hr-1	Hr-2	Hr-3	beta	Hr-4	Hr-5	Hr-6	Hr-7	Hr-8	Hr-9	Hr-10	Hr-11	Hr-12
3	first	b	beta	2	two			nothing				last

For reference, sample data with filter removed:

Hr-1	Hr-2	Hr-3	beta	Hr-4	Hr-5	Hr-6	Hr-7	Hr-8	Hr-9	Hr-10	Hr-11	Hr-12
2	first	a	alpha	1	one	mix		nothing				last
3	first	b	beta	2	two			nothing				last
4	first	c	charlie	3	three	of		nothing				last
5	first	d	delta	4	four			nothing				last
6	first	e	echo	5	five	blanks		nothing				last
7	first	f	foxtrot	6	six			nothing				last
8	first	g	golf	7	seven	and		nothing				last
9	first	h	hotel	8	eight	digita		nothing				last
10	first	i	india	9	nine	10		nothing				last

 

[MARK858]

Try the below (btw your sample didn't include anything that needed a partial/contains match )

Sub ColumnDContains()

ActiveSheet.Range("$A$1:$M$10").AutoFilter Field:=4, Criteria1:="*" & Range("D1") & "*"
End Sub

 

[ellison]

Wow, superb.... seeing now how to add in the "contains" by wrapping the asterisks .... Criteria1:="*" & Range("D1") & "*"

PS yes you got me, I probably should have changed cell D1 to something different - oops!

HUGE thanks

 

[MARK858]

You're welcome (Btw can you please use code tags when posting code in future, it makes it much easier to read and copy.... paste the code, select the code and then click the

icon in the posting window header)

 

[ellison]

Sorry - will do! And huge thanks for your help

 

[mumps]

Click here to download your sample file. First make a selection in D1. Next make a selection in Q1.

 

[Peter_SSs]

@mumps
Please note that is not an acceptable way to provide an answer in the forum. Refer to #4 of the Forum Rules
A linked file can be provided as support for an answer, but the answer itself (ie code/formulas/instructions etc) should be provided directly in the forum.

 

[mumps]

My apologies, Peter. I knew that, but wasn't thinking straight. I must be more careful.
Code in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "Q1" Then Exit Sub
    Application.ScreenUpdating = False
    With ActiveSheet
        If Target = "Equals" Then
            .Range("A1").CurrentRegion.AutoFilter 4, Range("D1").Value
        ElseIf Target = "Contains" Then
            .Range("A1").CurrentRegion.AutoFilter 4, Criteria1:="=*" & Range("D1").Value & "*"
        End If
    End With
End Sub

Code in Module1:

Sub ClearFilter()
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
End Sub

 

[Peter_SSs]

My apologies, Peter. I knew that, but wasn't thinking straight. I must be more careful.
Code in the worksheet module:

Thanks for updating.