Limit to Auto-Filter?

[ShaunD30]

I have a worksheet with 24k rows. Column C Contains the State, Column D contains the city.

Right now I am looking for accounts in chicago and surrounding cities..if I use Autofilter and just look for the city on the drop down..sometimes it isn't there..but if I choose the state (IL) and THEN look at the auto filtered cities..it's there.

Is there a limit to the number of unique values that an auto-filter can show?

Shaun

 

[schielrn]

In pre-2007 Excel the limit was 1000 and now I believe it is 10000 in Excel 2007.

Hope that helps.

 

[ShaunD30]

Am using 2000. That helps. Explains alot actually.

Now I have to figure out how to explain that to the boss man, and why this time the report is going to show much more than before .

Shaun (oops)

 

[schielrn]

Oh well it happens and you learn from it. You'll find that there are many other little quircky things about Excel.

 

[ShaunD30]

You would think that if you were working with a large spreadsheet..and you turned on the autofilter, and there were more unique values than what Auto-Filter could handle..perhaps a warning window? "You have exceeded the maximum Auto-Filter Limit"..something..anything..throw me a bone here.

Shaun

 

[ShaunD30]

Is there a way to tell if I have come close, hit, or gone over the 1000 limit?

 

[schielrn]

This formula will tell you how many unique values are within a range:

=SUM(IF(FREQUENCY(IF(LEN(C2:C25000)>0,MATCH(C2:C25000,C2:C25000,0),""), IF(LEN(C2:C25000)>0,MATCH(C2:C25000,C2:C25000,0),""))>0,1))

It needs to be confirmed with control+shift+enter and not just enter. Change the cell references as needed, but they cannot be a whole column in versions prior to 2007.

 

[ShaunD30]

Ok..that worked..but why Control+Shift+Enter? I've seen that posted a few times around here..what does that do exactly?

Shaun

 

[schielrn]

It signifies that it is an array formula. There is more literature at these links on array formulas and they can be very useful if used properly, but a lot of them can make the calculation time quite long:

http://www.cpearson.com/excel/ArrayFormulas.aspx

http://www.ozgrid.com/Excel/Arrays.htm

Hope that helps.

 

[ShaunD30]

Ah. I glanced over the first link..i understand..will be reading more about this.

Thanks again..I appreciate your help