|
extracting numbers with no more than 8-digits using advanced filtering
hi there...i am using advanced filtering (excel 2000) and am looking for a
way to further filter my data My Criteria currently reads: REGION MM-YY MM-YY =200212 =<200305 <=200312 Sample Filtered Data using above filter: REGION MM-YY ------------------------------ 1234 200212 123401 200212 12340101 200212 1234010101 200212 123402 200212 12340202 200213 1234020201 200213 I WANT TO ADD another criteria - under REGION i want to limit selections to only those region #'s which have UP TO 8-digits.. I don't need to see the entries with 10-digits (ie..1234010101, 1234020201)... the REGION COLUMN, by the way is formatted as "General" (long story - document imported from .txt and contains macros to reduce to this point) appreciate any assistance u can provide! Sandi |
On Wed, 27 Oct 2004 16:48:02 -0400, "Gauthier"
wrote: hi there...i am using advanced filtering (excel 2000) and am looking for a way to further filter my data My Criteria currently reads: REGION MM-YY MM-YY =200212 =<200305 <=200312 Sample Filtered Data using above filter: REGION MM-YY ------------------------------ 1234 200212 123401 200212 12340101 200212 1234010101 200212 123402 200212 12340202 200213 1234020201 200213 I WANT TO ADD another criteria - under REGION i want to limit selections to only those region #'s which have UP TO 8-digits.. I don't need to see the entries with 10-digits (ie..1234010101, 1234020201)... the REGION COLUMN, by the way is formatted as "General" (long story - document imported from .txt and contains macros to reduce to this point) appreciate any assistance u can provide! Sandi Assuming the first Region is in A8, use the following criteria: MM-YY MM-YY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 Note that there is no label in the column that contains the formulas. --ron |
Hi Ron...thanks for getting back to me! the columns aren't are parsed
together...just so i have it right, the =LEN(A8)<=8 is the first criteria column (yes, with no header)? i tried it, and it didn't work also (sorry if i should have mentioned earlier) does it matter if i used Advanced Filter - Copy to Another location the RESULTS are actually on sheet two Assuming the first Region is in A8, use the following criteria: MM-YY MM-YY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 Sandi ---------------------------------------------------------------------------- ----------------------------------- "Ron Rosenfeld" wrote in message ... On Wed, 27 Oct 2004 16:48:02 -0400, "Gauthier" wrote: hi there...i am using advanced filtering (excel 2000) and am looking for a way to further filter my data My Criteria currently reads: REGION MM-YY MM-YY =200212 =<200305 <=200312 Sample Filtered Data using above filter: REGION MM-YY ------------------------------ 1234 200212 123401 200212 12340101 200212 1234010101 200212 123402 200212 12340202 200213 1234020201 200213 I WANT TO ADD another criteria - under REGION i want to limit selections to only those region #'s which have UP TO 8-digits.. I don't need to see the entries with 10-digits (ie..1234010101, 1234020201)... the REGION COLUMN, by the way is formatted as "General" (long story - document imported from .txt and contains macros to reduce to this point) appreciate any assistance u can provide! Sandi Assuming the first Region is in A8, use the following criteria: MM-YY MM-YY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 Note that there is no label in the column that contains the formulas. --ron |
On Wed, 27 Oct 2004 21:14:30 -0400, "Gauthier"
wrote: Hi Ron...thanks for getting back to me! the columns aren't are parsed together...just so i have it right, the =LEN(A8)<=8 is the first criteria column (yes, with no header)? It can be in any column in the Criteria Range that does NOT have a header (or that has a header that is not one of the column labels). It needs to be entered on each of the rows, so in my example, I had specified the criteria range as being A1:C3. The formula would be entered in A2 and again in A3. i tried it, and it didn't work Must be something different about how you have things set up. Make sure your criteria range doesn't have a blank line in it. It worked here on the data you posted, filtering out the REGION's with more than 8 digits. also (sorry if i should have mentioned earlier) does it matter if i used Advanced Filter - Copy to Another location the RESULTS are actually on sheet two That should not make any difference. ----------------------------------- Here is the criteria range (in A1:C3) and the data (REGION is in A7): MM-YY MM-YY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 REGION MM-YY ------------------------------ 1234 200212 123401 200212 12340101 200212 1234010101 200212 123402 200212 12340202 200213 1234020201 200213 The criteria cells with the formula actually show as FALSE, since the formula is being evaluated. The result: REGION MM-YY 1234 200212 123401 200212 12340101 200212 123402 200212 12340202 200213 --ron |
Hi Ron...clueless here...
my criteria range is defined in range B1:D3 as follows: COL B.......COL C.....COL D row 1..... ......MM-YY.... MM-YY row 2..... TRUE.....=200212.....<=200305 row 3..... TRUE..... =200312 the TRUE field contains the formula u provided - =LEN(A7)<=8 A7 is where my DATA starts A6 is where the HEADERS begin - would that be it? any suggestions? sandi "Ron Rosenfeld" wrote in message ... On Wed, 27 Oct 2004 21:14:30 -0400, "Gauthier" wrote: Hi Ron...thanks for getting back to me! the columns aren't are parsed together...just so i have it right, the =LEN(A8)<=8 is the first criteria column (yes, with no header)? It can be in any column in the Criteria Range that does NOT have a header (or that has a header that is not one of the column labels). It needs to be entered on each of the rows, so in my example, I had specified the criteria range as being A1:C3. The formula would be entered in A2 and again in A3. i tried it, and it didn't work Must be something different about how you have things set up. Make sure your criteria range doesn't have a blank line in it. It worked here on the data you posted, filtering out the REGION's with more than 8 digits. also (sorry if i should have mentioned earlier) does it matter if i used Advanced Filter - Copy to Another location the RESULTS are actually on sheet two That should not make any difference. ----------------------------------- Here is the criteria range (in A1:C3) and the data (REGION is in A7): MM-YY MM-YY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 REGION MM-YY ------------------------------ 1234 200212 123401 200212 12340101 200212 1234010101 200212 123402 200212 12340202 200213 1234020201 200213 The criteria cells with the formula actually show as FALSE, since the formula is being evaluated. The result: REGION MM-YY 1234 200212 123401 200212 12340101 200212 123402 200212 12340202 200213 --ron |
On Wed, 27 Oct 2004 22:54:42 -0400, "Gauthier"
wrote: Hi Ron...clueless here... my criteria range is defined in range B1:D3 as follows: COL B.......COL C.....COL D row 1..... ......MM-YY.... MM-YY row 2..... TRUE.....=200212.....<=200305 row 3..... TRUE..... =200312 the TRUE field contains the formula u provided - =LEN(A7)<=8 A7 is where my DATA starts A6 is where the HEADERS begin - would that be it? any suggestions? sandi Send me a copy of your workbook, and I'll look at it. You may email it to me at the following address, but you will have to reverse all the letters and then replace the "AT" with the obvious. moc.enilnodlefnesorTAzyx --ron |
at the following address, but you will have to reverse all the letters
and then replace the "AT" Very slick, Ron. That should stop the spammers in their tracks! |
On Thu, 28 Oct 2004 14:57:26 -0500, Myrna Larson
wrote: at the following address, but you will have to reverse all the letters and then replace the "AT" Very slick, Ron. That should stop the spammers in their tracks! Thanks. It's also a "throw-away" address so if I start getting spam, I merely delete the address. --ron |
Hi Ron...fyi - i finally came up w/ a solution that works...
I changed the heading in B1 to LEN In the source data, I added another column with the heading LEN and the formula as directed to determine your TRUE result. I adjusted the named range data_range_for_filter to include the extra column. I ran the code with these modifications and it worked! Thanks for all your help and looking at my workbook for me! Sandi "Ron Rosenfeld" wrote in message ... On Wed, 27 Oct 2004 22:54:42 -0400, "Gauthier" wrote: Hi Ron...clueless here... my criteria range is defined in range B1:D3 as follows: COL B.......COL C.....COL D row 1..... ......MM-YY.... MM-YY row 2..... TRUE.....=200212.....<=200305 row 3..... TRUE..... =200312 the TRUE field contains the formula u provided - =LEN(A7)<=8 A7 is where my DATA starts A6 is where the HEADERS begin - would that be it? any suggestions? sandi Send me a copy of your workbook, and I'll look at it. You may email it to me at the following address, but you will have to reverse all the letters and then replace the "AT" with the obvious. moc.enilnodlefnesorTAzyx --ron |
On Fri, 29 Oct 2004 11:28:30 -0400, "Gauthier"
wrote: Hi Ron...fyi - i finally came up w/ a solution that works... I changed the heading in B1 to LEN In the source data, I added another column with the heading LEN and the formula as directed to determine your TRUE result. I adjusted the named range data_range_for_filter to include the extra column. I ran the code with these modifications and it worked! Thanks for all your help and looking at my workbook for me! Sandi Glad you found a workable solution for your data. Thank you for the feedback. --ron |
On Fri, 29 Oct 2004 11:28:30 -0400, "Gauthier"
wrote: Hi Ron...fyi - i finally came up w/ a solution that works... I changed the heading in B1 to LEN In the source data, I added another column with the heading LEN and the formula as directed to determine your TRUE result. I adjusted the named range data_range_for_filter to include the extra column. I ran the code with these modifications and it worked! Thanks for all your help and looking at my workbook for me! Sandi I just thought of another possibility. Since the regions are all numeric, why not a column called REGION in the Criteria Range, with the criteria being <100000000 ? --ron |
| All times are GMT +1. The time now is 05:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com