Excel Question - Filtering

G

Gimme

Registered User
Messages
81
I have an excel spreadsheet with 8,000 records and about 54 columns wide. My query is when trying to filter some of the records don't appear, i.e. I may want to filter a particular name - I go to filter - autofilter, dropdown arrow on the column that has the required info, scroll down in alphabetical order but the name is missing. I know it's there as I can use Ctrl + F to find but this will only give me one record at a time.

What can I do or am I doing something wrong?

Excel 97.
 
Gimme, I work with large spreadsheets all day long and I suspect that you have empty rows or cells in your table; by default Autofilter filters on adjoining cells, i.e any data afer an empty cell is not taken into account.

Here's how to ensure Autofilter catches everything
1- Select the entire table containing your data.
You can do this manually. With a big table like yours I suggest you select a cell in your table and then in 'Edit' menu select 'Goto'; in the dialogue that opens, click the button 'Special' and in that new dialogue select the option 'Current region'. Click OK and your whole table 'looks' selected.

2-Turn on Autofilter (Data / Filter /Autofilter)

3-Bob's your Uncle, you should be able to filter for the data you find.
 
d2x2 , tried that but still having problems.

I then tried sorting by the column where the name exists and tried again and it only lists names to row 3,405, yet as I said earlier I have about 8,000 records.

Anyone else got a solution?
 
Are you sure you dont have any other filter on which would restrict any additional filters? Sounds like it.
Scroll across all filters and release all of them, else drop filtering altogether and start from scratch before applying your new one.
 
The autofilter will only filter the first 1,000 unique records in your list. Therefore it would seem that by row 3,405 you have reached 1,000 unique records and the autofilter can't display anymore records.

To get around the problem, you could use the advanced filter which doesn't have this limitation.

Another way would be to filter another field in your range first. This might reduce the number of unique records to less than 1000 and include the name you are looking for.
 
Car, I definetly don't have any other filters on.

lemeister - I don't know enough on advanced filter - I usually only use it to take out dupes - can you throw more light on it for me?

Thanks
 
Gimme, an example of the advanced filter might work best.
If the range you want to filter is say from A1:C2000 with your column labels in row 1 (eg. Name; Number; Address) and you want to filter just a name, what you need to do is firstly create a separate table which includes your column label names and the row immediately below it, eg (E1:F2). This is what will be your criteria range.
Under the 'Name' label of your criteria range ie. cell E2, type the name you want to filter from your main range. Then select your entire range and click the advanced filter option from the tools menu. Your range should already be selected and the only thing you need to do is enter your criteria range, which in this example is (E1:F2). After entering this, click Ok, and your main range should only contain records with the name you wanted to filter on.
 
You could also select the "Custom" option when you click the filter button. This allows you to input the name or part of the name that you want.
I suspect that you have empty rows or cells in your table
Click to expand...
Another way of resolving this is to select one entire column, go to this menu: Edit->Go To...->Special... and select "Blanks". This will then select all the blank cells and you can select Edit->Delete... and select "Entire Row". Making sure of course that you select a column that should have an entry in every field (e.g., primary key or first name).
 
You must log in or register to reply here.
Menu
Log in
Register