Advanced filter
 

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 ???

Advanced filter
 

why not use

09110

if all your first numbers are 5 characters.

--
Regards,
Tom Ogilvy


Zoop wrote in message
...
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 ???

Advanced filter
 

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" wrote in message
...
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 ???

Advanced filter
 

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

In article , TroyW
wrote:

=AND(C11=$B$1,C11<=$B$2)

Advanced filter
 

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

In article , Tom Ogilvy
wrote:

why not use

09110

if all your first numbers are 5 characters.

--
Regards,
Tom Ogilvy


Zoop wrote in message
...
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 ???

Advanced filter
 

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


Zoop wrote:
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 ???


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Advanced filter
 

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.



--
Regards,
Tom Ogilvy


"Zoop" wrote in message
...


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

In article , Tom Ogilvy
wrote:

why not use

09110

if all your first numbers are 5 characters.

--
Regards,
Tom Ogilvy


Zoop wrote in message
...
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 ???

Advanced filter
 

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" wrote in message
...
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

In article , TroyW
wrote:

=AND(C11=$B$1,C11<=$B$2)

Advanced filter
 

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.

In article , TroyW
wrote:

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" wrote in message
...
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

In article , TroyW
wrote:

=AND(C11=$B$1,C11<=$B$2)