SQL style filtering of Excel worksheet

[hyarmion]

I am using Excel 2007.
Is it possible to set up a filter using SQL style syntax.
e.g. say I have a large worksheet of data that contains columns named 'Date' and 'Rego' (among many others), can I set up a filter something like this:

SELECT <rows of workbook> WHERE Date >='01/06/2010' AND Date <='30/06/2010' AND Rego = "A" OR Rego="B" OR Rego="C"

Can Excel do something like that?

 

[SkipVought]

hi,

SELECT <rows of workbook> WHERE Date >=#01/06/2010# AND Date <='30/06/2010' AND Rego = "A" OR Rego="B" OR Rego="C"

You must CONVERT your string representation of a date to a REAL DATE, which is a DateSerial NUMBER. That is what the # delimiters do.

Also, and VERY VERY important, your date string must NOT use the d/m/y structure. Bill Gates in Washington USA does not like that date string structure. Rather, use the unambiguous y/m/d structure!!!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hyarmion]

Thanks, SkipVought, I was aware that I would need to specify the dates differently. And I am aware that you Americans represent your dates differently to just about everyone else in the world.

But what you have not told me is how to do it in Excel, i.e where do I put the SQL like string? And how do I execute it?

 

[SkipVought]

Excel 2007

Data Tab > Get External Data > From other sources > From Microsoft Query ...

UNCHECK Use the Query Wizard...

Select Excel Files *

Navigate to your workbook

The Add tables window lists ALL sheets as SheetName$. If you do not see sheet names, hit the OPTIONS button and select all ck boxes.

Select a sheet Drag fields to grid.

File > Return date to Microsoft Excel

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

...there is also the option of using the Advanced Filter....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question