Using a "Find" based function instead of "Aut

[Err]

My problem is that I work as a data manager at a bear meat factory*. We use a numeric code which refers to the tenderness of each bears flesh and prior to harvest -I often have to sort that data based on the numeric code.


Excel has issues with screening numbers in Autofilter. You can not filter "begins with" or "ends with" if the data is numeric. For Example:







This same problem does not exist in "Find"





Since I seem to be relying on Autofilter for my queries, and it is possible to create a function similar to autofilter using the properly working "find" feature -I was wondering if anyone had already written a function that returns a range and functions similarly to autofilter?

*My job is really more boring and No bears were hurt in the writing of this post.

 

[NBVC]

See here for Microsoft's explanation/workaround suggestion:

http://support.microsoft.com/kb/170230


basically, you can covert the column to Text or if you need numbers, add another column which holds the "text" equivalents and filter by that column.

 

[Err]

You were just eaten by bears.

In the Bear harvesting business -we have a numeric standard which we pass between different bear ranches. You want to modify millions of records or perhaps change the standard because Microsoft has a bug.

This answer is somewhat unsatisfactory.

I think it would be much better if someone (in this case ME) merely writes a short function that searches a range with repeated finds -and then merely joins all the results as a single Results range.

The problem of course is that this solution is not optimized like built in functions.

I was also thinking that I can use an advanced filter in a similar way. Although I am not as familiar with Advanced filter.

I should also point out that sql does NOT have bugs like this. If you want to return a list of every datafield that begins with "7" you just specify it...

(like 7*)


The rest of the industry uses databases everyday without modifying their data to make a query. It is unclear why MS would suggest modifying OUR data to fix THEIR bug -but this bug has been known since 2000 and they haven't fixed it yet...

 

[NBVC]

You were just eaten by bears.

In the Bear harvesting business -we have a numeric standard which we pass between different bear ranches. You want to modify millions of records or perhaps change the standard because Microsoft has a bug.

This answer is somewhat unsatisfactory.

...

I didn't say that I wanted you to resolve your situation in any way...I merely was kind enought to point you to Microsoft's Knowledge Base showing that it is a known fact that you can't easily filter numeric data and I offered a suggestion...It's up to you to figure out how to deal with it.

 

[Err]

Nope, can't get out of it now. Your answer made Bill Gates smile. The bears are coming for you and you should be afraid... very afraid.


JK

 

[SydneyGeek]

Maybe this will avoid the bears...

Create another column / field that extracts the first number
=LEFT(A2,1)

That will be text and you can filter by it. Yes, you get to modify records but indirectly. You still keep the numbers... it's the only workaround I can think of now, short of trying some relational database stuff in Excel -- but if you're gonna do that, will Access serve you better?

Denis

 

[Err]

I should point out that this same bug is causing me a major headache because we can't autofilter on DATE's. Sure you can autofilter on a specific date but just try and limit it to BEFORE or AFTER that date.

All of your answers are really swell workarounds but they really don't address the issue (that Microsoft doesn't fix bugs). Well allegedly it's fixed in Microsoft 07 -can anyone verify this?

 

[Err]

I should point out that this same bug is causing me a major headache because we can't autofilter on DATE's. Sure you can autofilter on a specific date but just try and limit it to BEFORE or AFTER that date.

All of your answers are really swell workarounds but they really don't address the issue (that Microsoft doesn't fix bugs). Well allegedly it's fixed in Microsoft 07 -can anyone verify this?

 

[Cberry]

I should point out that this same bug is causing me a major headache because we can't autofilter on DATE's. Sure you can autofilter on a specific date but just try and limit it to BEFORE or AFTER that date.

I must be misunderstanding you, I autofilter on dates every day. Specifically looking for certain employees either hired or fired before or after a certain date.

 

[Err]

The problem with dates is a different one having to do with Cell Formatting