Filtering a mixed numeric/alpha ID no

I have a file of names and addresses which has an ID no some of which are numeric only and some of which have a single alpha character as a prefix. Those with the prefix were APPENDED to those with the numeric ID.

Now when I try to filter in EXCEL I find that the filter only lists those numeric IDs. I know that the records with prefixed IDs are there.

Can anyone enlighten me?

Steve McG

Hi Steve

It sounds as if you have applied a filter to (say) rows 1 - 200 and then appended data below it. If you remove and re-set your filter, does this solve your problem?

Regards
Alastair

Hi Alastair,

I'm afraid not. i had tried this before I posted and again after your reply but no go.

I wonder if it is anything to do with the size of my file:

12000 numeric ID records to which are appended 3 separate sets of alpha-prefixed records making a grand total of just short of 20,000 records for the whole file.

Thanks

Steve Mc

Hi Steve

Size should not be a factor. Try copying all the column to another sheet and see if the problem persists.
Regards
Alastair

Hi Alastair,

No that doesn't work either.

What is strange 'though is that when I sorted the file on the ID number but with the Z-A order option, this gave me the X
prefixed records followed by the S prefixes,then the G prefixes then the E prefixes with the records with numeric Ids last.

Then I filtered on the ID and was given the list of IDs starting at X prefixes...down to Es AND a number of numeric IDs but
not by any means all of the numerics.

Do I conclude that the filter process restricts the number of Ids to some magic number?

I would value any insights into this.

Thanks Steve

Hi oeldere,

First I will look to see if there are any empty rows and repost later.


Many thanks,
Steve

Hi Alastair & oeldere,

Re my filtering problem - I have now discovered that when I filter on the ID no I get a list of the first 10,000 Ids of the records in the spreadsheet.

This remains true when I sort the file on largest to smallest basis (thereby putting the alpha prefixed records first and the numerics last) - i.e, I sill get a list of the first 10,000 records.

Does Excel have a limit of 10,000?

Can anyone shed light on this, please

Steve Mc

Hi Steve

I have just tested a 20,000 line filter (in 2010) and there is an explicit warning that it only shows the first 10,000 rows. So in answer to your question - Yes there is a limit (but in 2010 it iis only a limit to the display - it will still find rows over the 10,000 if specifically requested (not available in 2003, if I remember correctly). (Time to upgrade, perhaps )

Regards
Alastair