Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Gauthier
 
Posts: n/a
Default 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




  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #3   Report Post  
Gauthier
 
Posts: n/a
Default

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



  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #5   Report Post  
Gauthier
 
Posts: n/a
Default

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





  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #7   Report Post  
Myrna Larson
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #9   Report Post  
Gauthier
 
Posts: n/a
Default

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



  #10   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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


  #11   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
extracting numbers from string Chris Dowell via OfficeKB.com Excel Discussion (Misc queries) 1 January 12th 05 10:37 PM
Extracting Values on one list and not another B Schwarz Excel Discussion (Misc queries) 4 January 7th 05 02:48 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
Sorting imported "numbers" Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 08:33 PM


All times are GMT +1. The time now is 07:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"