Filter by Row in Excel

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
EllenExcelAsked:
Who is Participating?
Advertise Here
 
byundtConnect With a Mentor Commented:
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
 
EllenExcelAuthor Commented:
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
 
byundtConnect With a Mentor Commented:
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
 
EllenExcelAuthor Commented:
Properly excellent answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.