Filtering a detailed list

[PJ O'Mahony]

Hi folks,
I am trying to filter a list for a given list of options, I wonder if anyone
can help me.

I am an ecologist, and I am trying to search for protected species within a
huge list of species that can be found on my site. The database contains
about 7-8000 species records, and I have a separate list of about 150
protected species.

Do you know if it is possible to filter the main list for the protected
species. Ideally I would like to be left with a short list of any protected
species found on the site.

All the best!

 

[Pete_UK]

I would suggest that you use a MATCH function with both workbooks open
so that in your master database you can tag those species which are
endangered. You give no details concerning the layout of data, so I
can only give you a general formula - something along these lines:

=IF(ISNA(MATCH(cell_ref,[protect.xls]sheet1!col_ref,
0)),"","protected")

where cell_ref is the sought item (species name?), protect.xls is the
filename of your list of protected species, with the data on sheet1,
and col_ref is the column that you are trying to match on. You can
copy this formula down all 8000 records in your main database once you
have substituted for your data layout.

Then you can fix the values in this column, so that you are not
dependent on the protected file anymore, and then you could apply
autofilter to the column to identify just those records with
"protected" against them.

Hope this helps.

Pete

 

[PJ O''Mahony]

That's fantastic Pete, thanks for all your help, and for such a quick reply!

So if I have the list of 8000 species in Column A, and the list of protected
species in Column A of 'protect.xls', the formula in cell B2 should be:
=IF(ISNA(MATCH(A2,[protect.xls]sheet1!A, 0)),"","protected").

I should then copy that all the way down Column B, and filter Column B for
'protected'. Am I correct, have I any commas or quotes in the wrong place?

Thanks again,
Nick

I would suggest that you use a MATCH function with both workbooks open
so that in your master database you can tag those species which are
endangered. You give no details concerning the layout of data, so I
can only give you a general formula - something along these lines:

=IF(ISNA(MATCH(cell_ref,[protect.xls]sheet1!col_ref,
0)),"","protected")

where cell_ref is the sought item (species name?), protect.xls is the
filename of your list of protected species, with the data on sheet1,
and col_ref is the column that you are trying to match on. You can
copy this formula down all 8000 records in your main database once you
have substituted for your data layout.

Then you can fix the values in this column, so that you are not
dependent on the protected file anymore, and then you could apply
autofilter to the column to identify just those records with
"protected" against them.

Hope this helps.

Pete

Hi folks,
I am trying to filter a list for a given list of options, I wonder if anyone
can help me.

I am an ecologist, and I am trying to search for protected species within a
huge list of species that can be found on my site. The database contains
about 7-8000 species records, and I have a separate list of about 150
protected species.

Do you know if it is possible to filter the main list for the protected
species. Ideally I would like to be left with a short list of any protected
species found on the site.

All the best!

 

[Pete_UK]

Just a slight amendment:

=IF(ISNA(MATCH(A2,[protect.xls]sheet1!A:A, 0)),"","protected")

Full-column ranges are written as A:A.

If you want to fix the values after copying the formula down, just
highlight the column with the formula in by clicking on the letter at
the top of the column, then click <copy>, then right-click and select
Paste Special | Values (check) | OK then <Enter>. Then you don't need
to have the protect.xls file open anymore.

Good luck.

Pete

That's fantastic Pete, thanks for all your help, and for such a quick reply!

So if I have the list of 8000 species in Column A, and the list of protected
species in Column A of 'protect.xls', the formula in cell B2 should be:
=IF(ISNA(MATCH(A2,[protect.xls]sheet1!A, 0)),"","protected").

I should then copy that all the way down Column B, and filter Column B for
'protected'. Am I correct, have I any commas or quotes in the wrong place?

Thanks again,
Nick

I would suggest that you use a MATCH function with both workbooks open
so that in your master database you can tag those species which are
endangered. You give no details concerning the layout of data, so I
can only give you a general formula - something along these lines:

=IF(ISNA(MATCH(cell_ref,[protect.xls]sheet1!col_ref,
0)),"","protected")

where cell_ref is the sought item (species name?), protect.xls is the
filename of your list of protected species, with the data on sheet1,
and col_ref is the column that you are trying to match on. You can
copy this formula down all 8000 records in your main database once you
have substituted for your data layout.

Then you can fix the values in this column, so that you are not
dependent on the protected file anymore, and then you could apply
autofilter to the column to identify just those records with
"protected" against them.

Hope this helps.

- Show quoted text -

 

[PJ O''Mahony]

Thanks again Pete, I really appreciate your help.

All the best
Nick

Just a slight amendment:

=IF(ISNA(MATCH(A2,[protect.xls]sheet1!A:A, 0)),"","protected")

Full-column ranges are written as A:A.

If you want to fix the values after copying the formula down, just
highlight the column with the formula in by clicking on the letter at
the top of the column, then click <copy>, then right-click and select
Paste Special | Values (check) | OK then <Enter>. Then you don't need
to have the protect.xls file open anymore.

Good luck.

Pete

That's fantastic Pete, thanks for all your help, and for such a quick reply!

So if I have the list of 8000 species in Column A, and the list of protected
species in Column A of 'protect.xls', the formula in cell B2 should be:
=IF(ISNA(MATCH(A2,[protect.xls]sheet1!A, 0)),"","protected").

I should then copy that all the way down Column B, and filter Column B for
'protected'. Am I correct, have I any commas or quotes in the wrong place?

Thanks again,
Nick

I would suggest that you use a MATCH function with both workbooks open
so that in your master database you can tag those species which are
endangered. You give no details concerning the layout of data, so I
can only give you a general formula - something along these lines:

=IF(ISNA(MATCH(cell_ref,[protect.xls]sheet1!col_ref,
0)),"","protected")

where cell_ref is the sought item (species name?), protect.xls is the
filename of your list of protected species, with the data on sheet1,
and col_ref is the column that you are trying to match on. You can
copy this formula down all 8000 records in your main database once you
have substituted for your data layout.

Then you can fix the values in this column, so that you are not
dependent on the protected file anymore, and then you could apply
autofilter to the column to identify just those records with
"protected" against them.

Hope this helps.

On Feb 6, 4:11 pm, PJ O'Mahony <PJ O'(e-mail address removed)>
wrote:
Hi folks,
I am trying to filter a list for a given list of options, I wonder if anyone
can help me.

I am an ecologist, and I am trying to search for protected species within a
huge list of species that can be found on my site. The database contains
about 7-8000 species records, and I have a separate list of about 150
protected species.

Do you know if it is possible to filter the main list for the protected
species. Ideally I would like to be left with a short list of any protected
species found on the site.

All the best!- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

You're welcome, Nick - thanks for feeding back.

Pete