Filtering Rows ?

[LucaBrasi]

I have a problem that I need to solve in a way other than the way I
always do it. OK, here goes:

I have a worksheet with 6 columns: The first column contains the name
of an employee. The next 5 columns contain 2 char US state
appeviations. These 5 columns represent the area of responsibility for
the employee in column 1. The distribution is more or less random, but
each of the employees has exactly 5 states. Now, what I would like to
do is type the 2 char code in an input box or cell, and have the
worksheet filter out any employee who does not have that state code in
any of the 5 columns, and output that report to a printer. In the
unfiltered worksheet, there are about 1800 records.
Now I have been doing this for a long time by importing the file to
Access, filter the records I want and then outputting to Excel. The
obvious reason for this is that I am proficient in Access, but not
Excel. Any info that could jump start me towards an Excel-only
solution would be much appreciated !

 

[Guest]

Hi

This is what you need to do. You have to use Data/Filter/Advanced Filter
option.

I am assuming your data is in A1 to say F1000. I am also assuming that row 1
has the headers, possibly
Name, State1, State2, State3, State4, State5

Make sure that your header is in a bolder and/or bigger font than the rest
of the data(This helps
Excel to figur out the header row.)

In H1 to L6 we will need to create a Criteria Range. Copy B1 to F1 and Paste
it in H1 to L1.
In effect you are creating the names of the fields on which you want to
apply the criteria.
If you want to search for CA, you will type CA in cells H2,I3,J4,K5 and L6.
Just like in Access
each separate row signifies an OR condition. (the criteria entered in the
same row is an AND
condition). So what we are saying here is that (State1 is CA) OR (State2 is
CA) OR..

Now click on the menu option Data/Filter/Advanced filter.

Select Copy to another location.
Click in the List range box and Type or otherwise indicate the source range
$A$1:$F$1000
Click in the Criteria range box and type or otherwise indicate the range
$H$1:$L$6
Click in Copy to: box and type the cell where you want to copy the extracted
data.
Click on OK.

Alok

 

[Joerg]

"Data => Filter =>Advanced Filter " is your friend.

Following example assumes that you put the 2 digit state code that you want
to
filter into A1.

Further let's assume that your list starts at A10 with a header line like
Name State1 State2 State3 State4 State5



Now copy this header line to A3
Into B4 (i.e below the header "State1") type: ="<>"&A1 (if A1 contains 'CA',
B4 should now read '<>CA')
Do the same for C4 to F4

Now put the cursor ito your data data list and and pull out the Advanced
filter menu.
'List range' should be highlighted (obviously thelist incl. headers)
'Criteria range' would be A3:F4
If you leave 'Action' as 'filter in list' and push the OK button, your data
list will collapse and show only records for employees who don't have the
state code of cell A! in any of there 5 columns.

Is that what you wanted to achieve?

Cheers,

Joerg

 

[LucaBrasi]

I appreciate the help[, but I was thinking more of a VBA solution,
requiring only the input of the state code. I'm looking for a solution
that is automated and a user with no Excel skills can do on their own.

 

[Debra Dalgleish]

There's a sample file here that you can adapt. It uses numbers, but you
can replace them with text:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0017 - Filter All Columns'