Formula Req'd - Autofilter limitation workaround
 

Based on a response to my earlier post, I learned that Excel's autofilter
will only work up to 1000 unique different values max.
I visited MVP Debra D's page http://www.contextures.com/xlautofilter02.html
as was suggested to me, and studied the options given.
I thought the 2nd one which reportedly extracts the first 2 or 3 letters for
use in a "pre-filter", was perhaps closer to providing me with a practical
solution.

However, my situation is a little different i.t.o. data, and I can't quite
figure out how to adapt the given formula viz. =LEFT(C2,2) to suit my
particular dataset.

I have some a column which contains a 7 digit number, with pre-fixed
zero(s). The data is naturally duplicated to roughly 60%, and lies across
some 3630 lines.
In all, I have about 1300 unique number entries, which obviously exceeds the
autofilter capability as was explained.

As I'm a novice at formulas, I would really appreaciate some help with a
suitable formula, that will ultimately consider ALL data down a particular
column in my worksheet, when a user does a "drop down list search".

Kind regards
Maurice

Formula Req'd - Autofilter limitation workaround
 

Hi

Are those numbers really numbers (formatted as "0000000"), or numeric
strings (cells are formatted as text)?
Into which numeric range do most of your numbers fall in?

P.e. when you have real numbers, and most of them are between 0 and 1000,
then you can use the formula
=INT(C2/100), which will give you groups 1, 2, 3, ..., 9, 10, 11 ... etc.
When you have numeric strings mostly between "0000001" and "0001000"), then
use formula
=LEFT(C2,4)

When those formulas are splitting your numbers too much, try dividing by
1000 or getting 3 leftmost characters.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Maurice" wrote in message
...
Based on a response to my earlier post, I learned that Excel's autofilter
will only work up to 1000 unique different values max.
I visited MVP Debra D's page
http://www.contextures.com/xlautofilter02.html
as was suggested to me, and studied the options given.
I thought the 2nd one which reportedly extracts the first 2 or 3 letters
for
use in a "pre-filter", was perhaps closer to providing me with a practical
solution.

However, my situation is a little different i.t.o. data, and I can't quite
figure out how to adapt the given formula viz. =LEFT(C2,2) to suit my
particular dataset.

I have some a column which contains a 7 digit number, with pre-fixed
zero(s). The data is naturally duplicated to roughly 60%, and lies across
some 3630 lines.
In all, I have about 1300 unique number entries, which obviously exceeds
the
autofilter capability as was explained.

As I'm a novice at formulas, I would really appreaciate some help with a
suitable formula, that will ultimately consider ALL data down a particular
column in my worksheet, when a user does a "drop down list search".

Kind regards
Maurice

Formula Req'd - Autofilter limitation workaround
 

One way .. along similar lines as in Debra's page, try tinkering with
something like this in the helper "pre-filter" col D, assuming the source
7-digit text numbers are running in C2 down:

In D2, copied down:
=IF(RIGHT(C2,4)+0<1800,"0-1800","1800")

The "+0" is to coerce the text number returned by RIGHT to a real number
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Maurice" wrote:
Based on a response to my earlier post, I learned that Excel's autofilter
will only work up to 1000 unique different values max.
I visited MVP Debra D's page http://www.contextures.com/xlautofilter02.html
as was suggested to me, and studied the options given.
I thought the 2nd one which reportedly extracts the first 2 or 3 letters for
use in a "pre-filter", was perhaps closer to providing me with a practical
solution.

However, my situation is a little different i.t.o. data, and I can't quite
figure out how to adapt the given formula viz. =LEFT(C2,2) to suit my
particular dataset.

I have some a column which contains a 7 digit number, with pre-fixed
zero(s). The data is naturally duplicated to roughly 60%, and lies across
some 3630 lines.
In all, I have about 1300 unique number entries, which obviously exceeds the
autofilter capability as was explained.

As I'm a novice at formulas, I would really appreaciate some help with a
suitable formula, that will ultimately consider ALL data down a particular
column in my worksheet, when a user does a "drop down list search".

Kind regards
Maurice

Formula Req'd - Autofilter limitation workaround
 

=IF(RIGHT(C2,4)+0<1800,"0-1800","1800")

If there's possibility of blank cells interspersed within col C,
try instead in D2, copied down:
=IF(C2="","",IF(RIGHT(C2,4)+0<1800,"0-1800","1800"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Formula Req'd - Autofilter limitation workaround
 

If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook, you might want
to consider the ArrayRowFilter function, which does not have the 1000
element limitation. It also doesn't have the Uniques Only capability,
so you would have to get rid of duplicates with additional code. The
following simple illustration works:

Sub abtest1()
Range("b1:b1500").Value = "a"
Range("c1:d2400").Value = ArrayRowFilter1(Range("A1:b2400"), 2, "a")
Application.ScreenUpdating = False
For i = 2400 To 1 Step -1
If Application.CountIf(Range("c1:c2400"), _
Range("C" & i).Value) 1 Then Range("C" & i).Resize(, 254).Delete
Next i
Application.ScreenUpdating = True
End Sub

Pretty slow, but it seems to work.

Alan Beban

Maurice wrote:
Based on a response to my earlier post, I learned that Excel's autofilter
will only work up to 1000 unique different values max.
I visited MVP Debra D's page http://www.contextures.com/xlautofilter02.html
as was suggested to me, and studied the options given.
I thought the 2nd one which reportedly extracts the first 2 or 3 letters for
use in a "pre-filter", was perhaps closer to providing me with a practical
solution.

However, my situation is a little different i.t.o. data, and I can't quite
figure out how to adapt the given formula viz. =LEFT(C2,2) to suit my
particular dataset.

I have some a column which contains a 7 digit number, with pre-fixed
zero(s). The data is naturally duplicated to roughly 60%, and lies across
some 3630 lines.
In all, I have about 1300 unique number entries, which obviously exceeds the
autofilter capability as was explained.

As I'm a novice at formulas, I would really appreaciate some help with a
suitable formula, that will ultimately consider ALL data down a particular
column in my worksheet, when a user does a "drop down list search".

Kind regards
Maurice