Solved

Filter by Row in Excel

Posted on 2014-02-12
4
663 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 81

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 81

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

776 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