Filtering Columns (without using the filter function!)

[ellison]

Hi, there is (an awful!) spreadsheet which is used in our organisation to help marry up what people want vs the most likely order code that needs to be used.

Effectively the most commonly used phrases have been entered into a spreadsheet to describe Colour, Length and Type of Material.

(I haven't been allowed to influence that, I'm just tiniest of tiny cogs!)

People use the "data / filter" function to find the order codes they need, but it's proving really time-consuming to have to click through to change the filters on each occasion.

Somebody asked (not unreasonably!) if there's a better way of doing the actual filtering - maybe something whereby there was a cell at the top of each column. And when text was entered into it, that became the search string which would then filter that column and show only cells containing that search string. (i.e. i.e. it would act in the same way as a filter, but without having to reset the filters each time)

I recommended using forms and drop-down menus but was shot down straight away because these spreadsheets have literally thousands of different entries for each field. So scrolling through those long lists to tick the box you need could be just as time-consuming.

I've put a quick table below which (hopefully) illustrates what we are trying to acheive:

	Colour	Length	Material	Order Code
Search Fields	*enter search sting*	*enter search sting*	*enter search sting*
	red	1	wood	AC764G
	orange	1.3	metal	54BCCP
	blue	1.3	plastic	ACCCA

<tbody>
</tbody>

(Thanks in advance for any pointers you could give!)
Best
Neil

 

[Peter_SSs]

Re: Filtering Columns (without tusing the filter function!)

But what we would like to do is add in multiple options on our filters... like an "OR" option
i.e. search Column A for "red" OR "yellow" or "blue" and then Column b for "plastic" and Column C for "rounded"

Change the layout a bit. Move the main data down a bit to allow for multiple criteria to added at the top. I have allocated A2:D5 as the range to enter the criteria. That is, up to 4 criteria per column. Adjust sheet (& code) to suit.

Book1
	A	B	C	D
1	Colour	Length	Material	Order Code
2	red	1	metal
3	blue		paper
4
5
6
7	Colour	Length	Material	Order Code
8	blue	1	paper	AC764G
Sheet3

Make sure Autofilter has been applied to the main data. A7:Dxx for me.
Then use this Worksheet_Change code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range, rCrit As Range
  Dim FieldCol As Long
  Dim sCrit As String

  With ActiveSheet
    Set rCrit = .Range("A2:D5")
    If Not Intersect(Target, rCrit) Is Nothing Then
      Application.ScreenUpdating = False
      If .FilterMode Then .ShowAllData
      For FieldCol = 1 To rCrit.Columns.Count
        sCrit = vbNullString
        For Each c In rCrit.Columns(FieldCol).Cells
          If Not IsEmpty(c.Value) Then sCrit = sCrit & "|" & c.Value
        Next c
        If Len(sCrit) Then .AutoFilter.Range.AutoFilter Field:=FieldCol, Criteria1:=Split(Mid(sCrit, 2), "|"), Operator:=xlFilterValues
      Next FieldCol
      Application.ScreenUpdating = True
    End If
  End With
End Sub

 

[ellison]

Re: Filtering Columns (without tusing the filter function!)

Thanks Peter t's excellent (I think!)....
I've set the sheet up as suggested with autofilter applied to but I'm getting an error.
The debug says highlights this piece of code:


.AutoFilter.Range.AutoFilter Field:=FieldCol, Criteria1:=Split(Mid(sCrit, 2), "|"), Operator:=xlFilterValues




Maybe I haven't set the sheet up correctly?!

 

[Peter_SSs]

Re: Filtering Columns (without tusing the filter function!)

... but I'm getting an error.

1. What is the full error message?

2. When you get the error & go to Debug, what do each of the highlighted variables show when you hover your cursor over them in that line of code?
.AutoFilter.Range.AutoFilter Field:=FieldCol, Criteria1:=Split(Mid(sCrit, 2), "|"), Operator:=xlFilterValues

3. What range did you set in this line of code ?

Set rCrit = .Range("A2:D5")

4. What cells have the AutoFilter arrows in? (For my sample it was A7:D7)

5. Is the worksheet Protected?

 

[ellison]

Re: Filtering Columns (without tusing the filter function!)

Hi Peter, I'm getting in a bit of a tangle here! Would it be possible to send you a snippet of the file I'm working on? I'm wondering if it's the volume of data on the spreadsheet that's causing the issue (& not your code!)
Best
Neil

 

[Peter_SSs]

Re: Filtering Columns (without tusing the filter function!)

Would it be possible to send you a snippet of the file I'm working on?

I'd prefer to try to resolve the problem without that - refer #4 of the Forum Rules - though an option later may be to upload a sample file to a file-share site and provide a link to that file.

For now, however, I'd really prefer if you addressed the questions from my previous post.

How big (rows & columns) is the file that you are testing on that is giving the error?

 

[ellison]

Re: Filtering Columns (without tusing the filter function!)

Many thanks for coming back Peter, appreciated.

Excel Version is Microsoft Office Business 365
Filename is: Peter_SS_test
It's saved as "unprotected", macro enabled .xlsm, Trusted file, with macros enabled.
Sheet is called Sheet1

There is no longer an error message appearing [but the results aren't appearing as they should]

Your second piece of code from this thread is added to the sheet.
the Range is set to A2:D40
Set rCrit = .Range("A2:D40")

Row 1 has a header row from A1 to D1 = colour, length, material, order code
From Row 1 to Row 41 is the criteria range

Row 41 is the start of the data to be filtered.
Row 41 has the same header row.
The autofilter with the arrows is set for Row 41.


The data goes from Row 41 to Row 143800 [yes, that's a lot of info!]
[I've only included info in Column A at the moment]

Issues seems to be:
If I put "blue" in the criteria, only cells which "equal" blue appear in the filter.
To get it to filter for cells which contain or start with etc, I'm using wildcards "*"

Issue 2:
The filter works but only as long as there is a restricted number of results to be shown in the filter.
eg. if I type into the criteria from A2 to A7: red, orange, green, black, white, purple
...then the correct results appear.

BUT if I put in the wildcards to widen the filter, the autofilter shows zero results.
e.g. in A2: A7, if I put in *red*, *orange*, *green*, *black*, *white*, *purple*

I'm wondering if either:
- I've set the sheet up wrong!
- There is too much for Excel to display in the filter & I'm beyond the limits of Microsoft Office Business 365

Any help much appreciated.

Best
Neil

 

[Peter_SSs]

Re: Filtering Columns (without tusing the filter function!)

Thanks for the additional information.

In post 10 when you started asking about OR conditions, there was no mention or examples of wildcards, so I assumed (apparently mistakenly) that you were just looking for exact matches.

With the size data you have and the quite large criteria range you have set, I'm not sure just how feasible it will be to achieve if you want to include wildcards. However, to consider that, can you clarify the following?

Do you want to be able to have both exact and wildcard filters? That is, would you want to be able to enter

red
*blue*
green*

and have that filter rows that have
exactly "red"
or
contain "blue" anywhere in the cell
or
start with "green"


Or will you just type, say
red
blue
green

and want it to filter any cells that contained any of those words either by themselves or combined? That is, just treat everything as a wildcard filter?

 

[ellison]

Re: Filtering Columns (without tusing the filter function!)

Hi Peter, apologies for not being clearer in my earlier post.
I think the first option from your last post would be more appropriate.
i.e. to enter
red
*blue*
green*

(in order to show that we are looking for exactly "red", contains "blue" and cells beginning with "green".

Best
NEil

PS I'm also wondering whether (due to the size of the file) this is the right approach!

 

[Peter_SSs]

Re: Filtering Columns (without tusing the filter function!)

I'm also wondering whether (due to the size of the file) this is the right approach!

I certainly think you are testing the limits here and you may want to ask in the Power BI forum as you may get a faster solution there if this is not sufficient for you.

I have tested the solution below with about 120,000 rows and 4 columns and it takes about 1 second to 'do its stuff' each time. That time will depend on your machine's resources and more rows &/or more column would increase the time.
If you are changing the filters a few at a time and this code is too slow then instead of this triggering each time one of the criteria cells is altered, we could make it so that no re-filtering was done until the user specifically triggers that (say by double-clicking a cell that turns red as soon as any cell in the criteria range is edited)

This method involves setting up a duplicate set of data columns as shown below for 4 columns. Once you have duplicated the headings like this and applied Autofilter so that it operates on all of those columns, the additional columns could be hidden though I suggest leaving them visible while testing.
This method automatically uses all rows from row 2 down to the row above the heading row as the criteria range.

Book1
	A	B	C	D	E	F	G	H
1	Colour	Length	Material	Order Code
2
3
4
5
6
7	Colour	Length	Material	Order Code	Colour	Length	Material	Order Code
Big

To test this method, in a standard module, put this code. Don't miss the single code line at the top.

Option Compare Text

Sub Special_Filter(rCrit As Range, HdrRw As Long, DataCols As Long)
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, uba As Long, r As Long, c As Long, rCritRws As Long
  Dim s As String
  
  rCritRws = rCrit.Rows.Count
  a = Intersect(ActiveSheet.UsedRange.Resize(, DataCols), Rows(HdrRw + 1 & ":" & Rows.Count)).Value
  uba = UBound(a)
  ReDim b(1 To uba, 1 To DataCols)
  For c = 1 To DataCols
    For r = 1 To rCritRws
      s = rCrit.Cells(r, c).Value
      If Len(s) Then
        For i = 1 To uba
          If a(i, c) Like s Then b(i, c) = 1
        Next i
      End If
    Next r
  Next c
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  With ActiveSheet
    If .FilterMode Then .ShowAllData
    With .AutoFilter.Range
      .Cells(2, DataCols + 1).Resize(uba, DataCols).Value = b
      For c = 1 To DataCols
        If WorksheetFunction.CountBlank(rCrit.Columns(c)) <> rCritRws Then .AutoFilter Field:=c + DataCols, Criteria1:=1
      Next c
    End With
  End With
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

.. and use this in the sheet's module. Note that this sets where the main header row is and how many columns of the main data there is.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim CriteriaRange As Range
  
  Const HeaderRw As Long = 7
  Const DataColumns As Long = 4
  
  Set CriteriaRange = Range("A2").Resize(HeaderRw - 2, DataColumns)
  If Not Intersect(Target, CriteriaRange) Is Nothing Then
    Special_Filter CriteriaRange, HeaderRw, DataColumns
  End If
End Sub

 

[SpillerBD]

Re: Filtering Columns (without tusing the filter function!)

A lot of PowerBI at the end level would utilize Slicers.
If using the latest Excel install from Office365, Tables have slicers available. Slicers are not always practical though, depending on the number of distinct values in a field.
If the range is not a Table, select a cell in the Range and use CTRL+T to invoke the Create Table.
Then the TABLE TOOLS ribbon will be available. Under the Table Tools Design ribbon, is the Insert Slicer option. You can have a slicer for multiple fields (columns). If you haven't used slicers before, choose one and play with its formatting options.
You can select multiple items from a slicer block using the CTRL+Right Click.