?
Solved

Use of Advanced Filter in Excel 2003

Posted on 2008-10-12
14
Medium Priority
?
1,729 Views
Last Modified: 2011-10-19
I have a list of records that contain a large number of columns.  One of the columns is the approval date, the other is name.  I want to use advanced filter to show the records that have an approval date later than January 1, 2002.  What formula would I type in the criteria Approval Date.

Also, can i use wild caracters to enter a formula to search all names that have E in the second position?

Thank you for the help

0
Comment
Question by:boulboul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 3

Expert Comment

by:DrunkenELF
ID: 22696913
Probably need to format the "X1" [assuming the criteria Cell) reference to be a date - at the mo, it is probably being viewed as the date serial number. Try:

="<=" & text(X1,"dd/mm/yyyy")

where dd/mm/yyyy is the format your dates are held in

2)
The question mark (?) wildcard character represents one characters in that position.


0
 
LVL 13

Expert Comment

by:WJReid
ID: 22696918
Hi,
Look at the attached. It uses B1:C2 as the criteria. You can change the date in B1 or the letter in C1 and then click the Filter button
FilterDate.xls
0
 
LVL 13

Expert Comment

by:WJReid
ID: 22696922
Hi,
Sorry, it uses B2:C3 as the criteria. Change the Date in B2 or the letter for the second letter in the name in C2. It will give dates > the entered date and names with the second letter as in C2.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 3

Expert Comment

by:DrunkenELF
ID: 22696949
were u lookin 4 a macro.. boulboul.. .. eh?
0
 
LVL 13

Expert Comment

by:WJReid
ID: 22696966
Hi,
There is no need to use the macro DrunkenELF, I thought it would be easier that's all. It will work using the Menu for Advanced Filter.

Bill
0
 
LVL 13

Expert Comment

by:WJReid
ID: 22696980
Hi Boulboul,

If you want to filter for the third letter being 'a', then simply change the formula in C3 to Mid(C10,3,1), changing the 2 after the first comma to a 3, it's as simple as that. If you only want to filter for the letter and not the date, change the criteria to C2:C3 in my example. You can even filter for 2 or more letters that are together e.g. 'be' by changing the formula in Cell C3 to Mid(C10,3,2), This will look at the Names in column C and filter out any with b as the 3rd letter and e as the 4th letter, the formula Mid(C10,3,2) means look at Cell C10, start at letter 3 and find the next two letters from there.
0
 

Author Comment

by:boulboul
ID: 22697348
DrunkenELF,

I am still not sure how to use the formula you provided.   Attached is a workbook showing an example of the data I am using.  What would be the formula to enter in Cell C2 so I can only show the Projects who have a date of approval after Jan 1, 2002.

Thank you
Book1.xls
0
 
LVL 3

Expert Comment

by:DrunkenELF
ID: 22697419
Dear Boul,

I have already created the advanced filter in the attached  file.

List Range = $A$4:$C$24
Criteria Range = $A$1:$C$2
Advanced-Filter.xls
0
 
LVL 3

Accepted Solution

by:
DrunkenELF earned 400 total points
ID: 22697471
Dear Boul,

Apologies for the confusion. there is no format issue in ur file. hence if u use a simple formula mentioned below in C2 should be fine

Formula in C2 =">1/1/2002"

In Advanced Filter
------------------------
List Range = $A$4:$C$24
Criteria Range = $A$1:$C$2


Plz.. find attached revised file. and hope the previous file also will be useful in the future when u face some date format issues.

Lemme know incase if u need any further clarifications.

Cheers,
D-ELF
Advanced-Filter-Rev.xls
0
 

Author Comment

by:boulboul
ID: 22697480
Thank you.. It works, How about the second half of the question?  For example, if I want to filter the projects that have letter "a" in the third position, what should I enter in A2?
0
 
LVL 13

Expert Comment

by:WJReid
ID: 22697658
Hi,

In case you are interested in using code to do the filtering, you can use the Worksheet_Change() event. I have attached your file with the code input. I have added a letter position cell in D3, where you can change the position of the letter and if you enter the desired letter in cell B3, the database will filter, you can then add a date in cell C3 and the database will again filter. As you delete the criteria, the database will filter on the decreasing criteria.
It is an automatic filter as soon as you change the criteria and you can filter for any letter in any position
I have hidden Row 2 which contains the criteria, as you won't need to touch this row.
FilterDate.xls
0
 
LVL 3

Assisted Solution

by:DrunkenELF
DrunkenELF earned 400 total points
ID: 22697727
Dear Boul,

Please use the below formula in cell "A2"

="=??a*"

C attached file.

Cheers,
D-ELF
Advanced-Filter-Rev-1-.xls
0
 
LVL 3

Expert Comment

by:DrunkenELF
ID: 22705779
Dear Boul,

Did u try it ..... eh?

Cheers!

D-ELF
0
 

Author Comment

by:boulboul
ID: 22709487
Hi D-ELF,

Just tried the text criteria today.. It works perfectly...

Yes, I realized that i don't need the TEXT function in the formula for the date criteria and that =">1/1/2002" works.

Full points awarded.

Thank you.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question