Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ??? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ??? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ??? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ??? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ??? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |