Solved

Filter by Row in Excel

Posted on 2014-02-12
4
648 Views
Last Modified: 2014-02-13
Hello,

In the attached, ( ExpertsExchangeExample2.xlsm.xlsx )  I have several groups of data. This is produced automatically from 3rd party software.

I would like a way, if possibly, to filter through this by ROW, rather than Column.

For example, in each group we have a Row labelled Responsibility Key. I want to Filter by Responsibility Key.

So in the attached, if I wanted to filter by Responsibility Key 'Collections', then only those with that Key will appear.

The real dataset is 4500 rows long, this is a small example.

Another idea was getting this data into a database such as Access and sorting like that.

Thanks for any advice,

Alex
0
Comment
Question by:EllenExcel
  • 2
  • 2
4 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39854724
This Comment describes both a manual and automatic method for performing your filtering.

Preparation:
1. Set up auxiliary column formulas (described below)
2. Create an AutoFilter for all your data, starting in row 5

Both Manual and Automatic methods use auxiliary column formulas in cells D6 and E6. These formulas are copied down until the end of your data.
=IF(A6="","","Responsibility " & COUNTIF(A$6:A6,"Responsibility"))
=IF(D6="","",(COUNTIFS(D:D,D6,A:A,A$2,B:B,B$2)+COUNTIFS(D:D,D6,A:A,A$2,C:C,B$2))>0)

The auxiliary column formula in column D returns the a sequential number for each group, using the format "Responsibility #"  The auxiliary column formula in column E returns TRUE for all rows in a given RESPONSIBILITY group provided that the Key in cell A2 matches a value in column A and a Value in columns B or C matches cell B2. Both formulas return an empty string (looks like a blank) if column A is blank. The column E formula returns FALSE if column A is not blank, but either the Key isn't used in that group or its value isn't found in columns B or C.

Manual method:
1.  Enter a key in cell A2 (Field Name, Responsibility Application, etc.)
2.  Enter a value in cell B2
3.  Use the AutoFilter in cell E5 and filter for TRUE
Repeat steps 1-3 as desired.

Automatic method, using a Worksheet_Change macro to automate step 3 of Manual method:
1.  Enter values in cells A2 and B2
2.  The following Worksheet_Change macro will perform the AutoFilter:
'This code must go in the code pane for the worksheet being watched. It won't work at all if installed anywhere else!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Union(Range("A2"), Range("B2"))
If Intersect(targ, Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Range("E5")
    If Application.CountA(targ) = targ.Cells.Count Then
        .AutoFilter Field:=.Column, Criteria1:="True"
    Else
        .AutoFilter Field:=.Column
    End If
End With
Application.EnableEvents = True
End Sub

Open in new window

ExpertsExchangeExample2Q28363142.xlsm
0
 

Author Comment

by:EllenExcel
ID: 39856056
Hi,

That is absolutely great work, thank you very much for helping with that.

I will accept this, but I wanted one bonus question.

Is it possible for this to work with partly correct search terms?

So "collect" would still find Collections. General would find "General Ledger" etc?

Or does the method need the fully correct term?
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 39856514
EllenExcel,
Since the proposed methods are already using AutoFilter, you can use * as a wild-card character. In other words "Collect*" will find "Collections" and "General*" will find "General Ledger".

Brad
0
 

Author Closing Comment

by:EllenExcel
ID: 39856753
Properly excellent answer
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now