Solved

Filter by Row in Excel

Posted on 2014-02-12
4
678 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

696 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