filtering multiple columns with ranges

[Kenlyn]

using xls2002 - i have 4 columns of data- the first column has an
account # - there may be several records with the same account # - I
need to compare the number of records with the same account # and
compare the data in the other 3 columns - for only those records wiht
the same account # -

I would like to write a formula that asks the queston - give me all
Acct#s that have less than 4 ( or x number) different records, with
all LPDate = 00/00/0000 & all for those records Dlisted date >
01/10/2002 & all balances less than 150 - I just can't get my hands
around the formula to check multiple Acct#'s ( they can be sorted in
order )


ACCT# LPDate DLISTED BALANCE
123211 00/00/0000 10/31/2003 3214
123211 00/00/0000 8/9/2002 76
123213 12/21/2003 9/3/2003 1412
123331 00/00/0000 01/23/2002 11
123331 10/2/2003 9/7/2003 654
12321 11/3/2003 03/10/2000 234
123213 00/00/0000 11/12/2003 22
909848 00/00/0000 01/31/1999 1321


As always, your help is appreciated .

Thanks - Kenlyn

 

[Debra Dalgleish]

You can do this with an Advanced filter. In the criteria range, leave
the heading cell blank, and in the cell below, enter the following formula:

=AND(COUNTIF(A:A,A2)<4,B2="00/00/0000",C2>DATE(2002,1,10),D2<150)

There are instructions for advanced filters here:

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