help filtering for unique records - urgent :)

  • Thread starter Thread starter crazidazi3
  • Start date Start date
C

crazidazi3

I can't find my org post, so reposting..sorry.

I have a worksheet that has multiple columns, with multiple dates..

Acct Nu Assign Dt Date 1 Date 2 Date 3 Date 4 Date 5
1234567 10/10/09 10/13/09 10/16/09 10/19/09 10/22/09 10/25/09
2345678 10/15/09 10/18/09 10/21/09 10/24/09 10/27/09 10/30/09
3456789 10/12/09 10/15/09 10/18/09 10/21/09 10/24/09 10/27/09
4567890 10/11/09 10/14/09 10/17/09 10/20/09 10/23/09 10/26/09
5678901 10/12/09 10/15/09 10/18/09 10/21/09 10/24/09 10/27/09
6789012 10/17/09 10/20/09 10/23/09 10/26/09 10/29/09 10/12/09

I need to filter for all acct #s with a follow up date of 10/24/09 (or
whatever date I need). I have tried all versions of advanced filter, but it
does not allow you to search by multiople date across columns. Please help!
 
--In the next available column say J. Enter the query date in cell J1

--In J2 enter the below formula and copy/drag down
=IF(COUNTIF(B2:I2,$J$1),"Found","")

--Now filter by that column

If this post helps click Yes
 
Let's say the date you want to find is in cell A1. Create an additional
helper column with formula:
=ISNUMBER(MATCH($A$1,C2:G2,0))
(where C2:G2 contains the dates you want to check)
copied down

you can then filter on your helper column for a value of TRUE.
 
Hi,

Assume that your data is in range B4:H10 (including the header row). In
A14, type the date. In cell B14, enter =COUNTIF(C5:H5,$A$14)>0. In cell
B13, type condition1 and in B17, type Acct Nu (the same heading as in the
source range). Now go to Data>Filter>Advanced Filter>Copy to another
location. In the list range, select A4:H10. In the criteria range, select
B13:B14. In the copy to box, select B17. Click on OK.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top