Formula Req'd - Autofilter limitation workaround

[Guest]

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

 

[Arvi Laanemets]

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.

 

[Guest]

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

 

[Guest]

=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"))

---

 

[Alan Beban]

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