Solved

# Filter by Row in Excel

Posted on 2014-02-12
675 Views
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.

Alex
0
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
• 2
• 2

LVL 81

Accepted Solution

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
``````
ExpertsExchangeExample2Q28363142.xlsm
0

Author Comment

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

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".

0

Author Closing Comment

ID: 39856753
0

## Featured Post

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
###### Suggested Courses
Course of the Month6 days, 6 hours left to enroll