Formula/Macro to delete rows that do not meet criteria from a list?

If I have a list of data on one sheet, is there any way I can use a
formula to compare another sheet to that list and delete rows that do
not meet the criteria?

(example - sheet 2 has a list of activities, so if cell a1 in sheet 1
does not match any of the activities listed in sheet 2 column a, then
the row will delete itself)

Probably not, but Im just curious.

To delete the row automatically will require VBA

A non-programmatic method would is to use a helper column in sheet1, and put

=MATCH(A1, Sheet2!A$1:A$100,0) and copy down (change range references as
needed)

If you could have extra spaces in your data then
=MATCH(TRIM(A1), TRIM(Sheet2!A$1:A$100),0) and hit Control+Shift+Enter after
typing it in. Then copy down. If not entered properly, you'll get #VALUE

If there's a match it will return a number. If not it will return #N/A.
Copy, then click Edit/Paste Special - Values. Sort your data in Sheet1 using
this new row as the key. The #N/A's s/b bunched at the top or bottom.
Delete the rows w/#N/A, then delete the helper column.



"S Davis" wrote:

> If I have a list of data on one sheet, is there any way I can use a
> formula to compare another sheet to that list and delete rows that do
> not meet the criteria?
>
> (example - sheet 2 has a list of activities, so if cell a1 in sheet 1
> does not match any of the activities listed in sheet 2 column a, then
> the row will delete itself)
>
> Probably not, but Im just curious.
>
>

Try something like this:

With
The master activity list in cells A1:A10 on Sheet2
The new list in cells A1:A100 on Sheet1, with A1 as the title (eg: Activity)

On Sheet1:
B1: TEST
B2: =ISNUMBER(MATCH(A2,Sheet2!$A$1:$A$10,0))
(That formula returns TRUE for matches, otherwise FALSE.)

Copy that formula down as far as you need.

THEN...
Use an autofilter on the TEST field to only display FALSE.

Select the filtered range
<edit><delete>
(Excel only allows row deletions in filtered lists)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"S Davis" wrote:

> If I have a list of data on one sheet, is there any way I can use a
> formula to compare another sheet to that list and delete rows that do
> not meet the criteria?
>
> (example - sheet 2 has a list of activities, so if cell a1 in sheet 1
> does not match any of the activities listed in sheet 2 column a, then
> the row will delete itself)
>
> Probably not, but Im just curious.
>
>