Home 
Search 
Today's Posts 
#1




extracting numbers with no more than 8digits 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 MMYY MMYY =200212 =<200305 <=200312 Sample Filtered Data using above filter: REGION MMYY  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 8digits.. I don't need to see the entries with 10digits (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 
#2




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 MMYY MMYY =200212 =<200305 <=200312 Sample Filtered Data using above filter: REGION MMYY  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 8digits.. I don't need to see the entries with 10digits (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: MMYY MMYY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 Note that there is no label in the column that contains the formulas. ron 
#3




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: MMYY MMYY =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 MMYY MMYY =200212 =<200305 <=200312 Sample Filtered Data using above filter: REGION MMYY  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 8digits.. I don't need to see the entries with 10digits (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: MMYY MMYY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 Note that there is no label in the column that contains the formulas. ron 
#4




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): MMYY MMYY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 REGION MMYY  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 MMYY 1234 200212 123401 200212 12340101 200212 123402 200212 12340202 200213 ron 
#5




Hi Ron...clueless here...
my criteria range is defined in range B13 as follows: COL B.......COL C.....COL D row 1..... ......MMYY.... MMYY 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): MMYY MMYY =LEN(A8)<=8 =200212 =<200305 =LEN(A8)<=8 <=200312 REGION MMYY  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 MMYY 1234 200212 123401 200212 12340101 200212 123402 200212 12340202 200213 ron 
#6




On Wed, 27 Oct 2004 22:54:42 0400, "Gauthier"
wrote: Hi Ron...clueless here... my criteria range is defined in range B13 as follows: COL B.......COL C.....COL D row 1..... ......MMYY.... MMYY 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 
#7




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! 
#8




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 "throwaway" address so if I start getting spam, I merely delete the address. ron 
#9




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 B13 as follows: COL B.......COL C.....COL D row 1..... ......MMYY.... MMYY 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 
#10




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 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
extracting numbers from string  Excel Discussion (Misc queries)  
Extracting Values on one list and not another  Excel Discussion (Misc queries)  
Sorting when some numbers have a text suffix  Excel Discussion (Misc queries)  
Sorting imported "numbers"  Excel Discussion (Misc queries)  
Seed numbers for random number generation, uniform distribution  Excel Discussion (Misc queries) 