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
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="","","Responsibili
=IF(D6="","",(COUNTIFS(D:D
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:
Open in new window
ExpertsExchangeExample2Q28363142.xlsm