Advanced filtering does not seem to work

[Ralph Klijn]

Hello everyone,

First of all, I am very new to the forum and quiet a quite inexperienced Excel user, so my apologies beforehand if I frame my question wrong because of misunderstandings etc.

So, this is my situation:

I have a list of panel data which is too large to handle by hand: this data is in three columns (column headers: year, id, rank). I have a column which contains all the id values I need the information for.

I tried to use the 'Advanced filter' tool to filter by id. However, when I look at my filtered data, rows appear as well in which I find id that were not in my set of selected data.

Anyone knows a solutions to my problem?

Thank you in advance.

Best regards,
Ralph

[xlnitwit]

Hi,
By default, the filter will include any IDs that start with any of the items in your list.So if your list contains ID 123 for example, the filter would include IDs 123, 1234, 1236 etc within the results. Does that explain what you are seeing?

[Ralph Klijn]

It guess that is my main issue. Thank you. Is there a way to fix it?

[xlnitwit]

You could either format the criteria range as Text and then enter =123 etc for each number, or use a formula criterion by leaving a blank cell, then in the cell underneath, enter the formula
=ISNUMBER(MATCH(A2),criteria_list_range,0))
where A2 is the first ID cell in your data and criteria_list_range is the address (in absolute form) of your criteria range. Then specify these two cells as the criteria range rather than your actual list.

[Ralph Klijn]

Thank you very muchfor the reaction.

I tried formatting as text, but it seems to have no effect on the outcome. As for the entering of criteria, I have a ready made list of over >1000 values as criteria, so I cannot really enter them all again.

When I try =ISNUMBER(MATCH(A2),criteria_list_range,0)), I get that I have entered too few arguments.

Best regards

[Ralph Klijn]

Also, my identifier criteria are letter codes (e.g. AAI, AA), not numbers.

[xlnitwit]

It makes no difference if they are not numbers. I suspect that for your regional settings you need a semicolon thus
=ISNUMBER(MATCH(A2);criteria_list_range;0))