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

 

[JonXL]

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

Are you saying a VBA solution is out? Or just one using forms?

 

[ellison]

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

Hi, either or both!
I guess the advantage of VBA would be that we could easily apply this to different filenames, sheetnames and column names?
But the advantage of using forms is that it could still work on files which are already creakingly large (and some our files have literally hundreds of thousands of rows with up to 30-50 columns of data - which is creakingly large in my book!)
Thanks

 

[vcoolio]

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

Hello Ellison,

Here's one way using VBA:-

Sub FilterThings()

           Dim fSearch As String
           Dim critSearch As String

fSearch = InputBox("Please enter the number of the column to be filtered.")
critSearch = InputBox("Please enter a value to be filtered from the selected column.")
If fSearch = vbNullString Then Exit Sub
If critSearch = vbNullString Then Exit Sub

With Sheet1.[A1].CurrentRegion
           .AutoFilter fSearch, critSearch
End With

End Sub

The code uses two Input Boxes that ask you to:-
1) Enter a column number to filter.
2) The value to be filtered.

Following is the link to a small sample that you can play with. Click on the "RUN" button to see it work.

http://ge.tt/1A0w3pp2

I hope that this helps.

Cheerio,
vcoolio.

 

[ellison]

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

Hi that's a great one to start working on - thanks very much indeed!

 

[vcoolio]

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

Excellent Ellison! I'm glad that I was able to help in some way.

Cheerio,
vcoolio.

 

[Peter_SSs]

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

The following would be more automatic & I think easier to use.

Set up as shown below, with your headings in row 2, data from row 3 and use row 1 cells as the search boxes.
Make sure you have the AutoFilter set up so that the filter arrows are on the headings in row 2.
Now,
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering/deleting search values (or partial values) in row 1.

As soon as values are entered, modified, deleted in row 1, the filters should automatically adjust to reflect those changes.

Test in a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim FilterRangeFirstCol As Long, FieldCol As Long
  Dim sCrit As String
  
  If ActiveSheet.AutoFilterMode Then
    With ActiveSheet.AutoFilter.Range
      Set Changed = Intersect(Target, .EntireColumn, Rows(1))
     If Not Changed Is Nothing Then
        FilterRangeFirstCol = .Column
        For Each c In Changed
          sCrit = "*" & c.Value & "*"
          FieldCol = c.Column - FilterRangeFirstCol + 1
          If sCrit = "**" Then
            .AutoFilter Field:=FieldCol
          Else
            .AutoFilter Field:=FieldCol, Criteria1:=sCrit
          End If
        Next c
      End If
    End With
  End If
End Sub

Excel Workbook
	A	B	C	D	E
1	Search Fields ->	blue
2		Colour	Length	Material	Order Code
5		blue	1.3	plastic	ACCCA
8		dark blue	1.3	paper	CCC
9
Sheet1

 

[ellison]

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

That is excellent, many thanks. It works perfectly on cells containing text, although it doesn't seem to work on the field containing the numbers?

 

[Peter_SSs]

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

That is excellent, many thanks. It works perfectly on cells containing text, although it doesn't seem to work on the field containing the numbers?

The following code should work for text or partial text and for exact numbers. It will not filter 363 if say 3 is entered in the search field but it would filter "2.3X"
This code would replace the previous code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim FilterRangeFirstCol As Long, FieldCol As Long
  Dim sCrit As Variant
  
  If ActiveSheet.AutoFilterMode Then
    With ActiveSheet.AutoFilter.Range
      Set Changed = Intersect(Target, .EntireColumn, Rows(1))
     If Not Changed Is Nothing Then
        FilterRangeFirstCol = .Column
        For Each c In Changed
          FieldCol = c.Column - FilterRangeFirstCol + 1
          If IsEmpty(c.Value) Then
            .AutoFilter Field:=FieldCol
          Else
            If IsNumeric(c.Value) Then
              sCrit = c.Value
            Else
              sCrit = "*" & c.Value & "*"
            End If
            .AutoFilter Field:=FieldCol, Criteria1:=sCrit
          End If
        Next c
      End If
    End With
  End If
End Sub

 

[ellison]

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

Hi, the code above (that Peter posted )so that we can use a "multi-filter" on our spreadsheets has been BRILLIANTLY helpful.

In short, it filters all of the data on a given spreadsheet for whatever search strings are placed in Row 1.
So at the moment we could filter Column A for "red", Column B for "plastic" and Column C for "rounded".

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"

(We are on a mission to clean up our data and collating all the different reports was like pulling teeth. Using Peter's code has been superb and tweaking it to include this feature would be the cherry on the cake!)

Best

Neil