Advanced filter

[Zoop]

Part of my procedure needs to filter a list containing bookings for
rental items. each item has a code number such as '09110-' or
'09110-03'

when I perform an advanced filter on this list using a code number such
as '09110-' as the criteria I do not get the data matching the
criteria, I get very unpredictable results including codes nothing like
the criteria, if I use a code number such as '09110-03' it works fine.

It seems that the'-' at the end of the number causes the filter to not
function properly.
I have tried using auto filter and it works fine but it is not suitable
to my needs. I have also tried reformatting the cells as
text/general/number, but this did not help.

Any Ideas ????

One more thing on the subject is there a way to use a '>' or '<' type
of operator in the criteria to filter rental items booked before or
after a specified date ???

 

[Tom Ogilvy]

why not use

09110

if all your first numbers are 5 characters.

 

[TroyW]

Zoop,

Yes, you can use dates to restrict the filter. You can also use the same
technique to filter the data for your code number, "09110-"

Create a worksheet with the cells set up as follows:

A1: FilterDateBeg
A2: FilterDateEnd
A3: FilterCode
B1: 09/01/2003
B2: 09/30/2003
B3: 09110-

D1: DummyCode
D2: =LEFT(D11,LEN($B$3))=""&$B$3&""
E1: DummyDate
E2: =AND(C11>=$B$1,C11<=$B$2)

C10: RentalDate
C11: 08/27/2003
C12: 08/30/2003
C13: 09/14/2003
C14: 09/23/2003
C15: 09/25/2003
C16: 10/01/2003

D10: CodeNum
D11: 09110-01
D12: 09110-14
D13: 09110-17
D14: 09123-99
D15: 09110-01
D16: 09110-10

From the menubar: Data | Filter | Advanced Filter...
Filter the list, in-place radio button is selected
List Range: $C$10:$D$16
Criteria Range: $D$1:$E$2

Click OK.

The data list should filter to show only rows with data between the dates
specified in cells B1 and B2, and with a CodeName of "09110-". Rows 13 & 15
should be displayed.

IMPORTANT: The names in cells D1 and E1 must not match any column header
names in Row10, the top row of the dataset. In this example, the names
(DummyDate & DummyCode) can not match the names (RentalDate & CodeNum). You
also need to be careful about how entries are made into cell B3. This needs
to be a text entry and not a numeric entry. Be careful not to use invalid
dates like: Feb 30th, Jun 31st, Sep 31st, etc.

Changing the values in cells B1, B2, & B3 and then reissuing a new data
filter command will display a different filter list result. Let me know if
this works for you.

Troy

 

[Zoop]

Thanks Troy,

That works great for the dates excellent, but I need to differentiate
between codes like '09110-' and '09110-10', your technique filters
anything with with'09110-' as a prefix.

Thanks For your help
Zoop

 

[Zoop]

Hi Tom,
If only it where that easy, the list is a dbf file generated by an
inventory tracking application, Thats just the way it spits it out.
and 09110 is not necessarilly the only prefix there a hundreds of others

Thanks
Zoop

 

[Debra Dalgleish]

In the criteria area, precede the code with an apostrophe, and the
filter should work correctly. Using your example: '09110-

 

[Tom Ogilvy]

You want to get items with the specific code 09110- , in otherwords that is
the complete code. Is that your question?

Code Number Code Number
=">=09110-" ="<=09110-"


That worked fine for me.

 

[TroyW]

Zoop,

I thought you wanted the "prefix" version. This version will match
absolutely. Note the following changes to my previous post.

D1: The column name MUST now match the column name in the Data Table, cell
D10.
D2: The formula has changed to: ="="&""&$B$3&""
(those are all double-quotes, no single-quotes)

Troy

==== Updated Version Below ====
Create a worksheet with the cells set up as follows:

A1: FilterDateBeg
A2: FilterDateEnd
A3: FilterCode
B1: 09/01/2003
B2: 09/30/2003
B3: 09110-

D1: CodeNum
D2: ="="&""&$B$3&""
E1: DummyDate
E2: =AND(C11>=$B$1,C11<=$B$2)

C10: RentalDate
C11: 08/27/2003
C12: 08/30/2003
C13: 09/14/2003
C14: 09/23/2003
C15: 09/25/2003
C16: 10/01/2003

D10: CodeNum
D11: 09110-01
D12: 09110-
D13: 09110-
D14: 09123-99
D15: 09110-
D16: 09110-10

 

[Zoop]

Thanks Troy,

That works perfectly.
Once again I have been saved by the vast knowledge residing in this
news group.
Many thanks to yourself, Tom, and Debra.

Till next crisis
Zoop.