need help filtering entries

[jason justice]

i am using excel 2001 for a mac.
in one spreadsheet i have a column of email addresses.
in another spreadsheet i have a list of email addresses i
need to remove
from the other spread sheet.

any idea of how i can do this?

if i paste the removes into the same column as the
original and filter it
all it does is remove the duplicate entry. i need to
remove both entries.

thanks in advance for the help.

 

[Debra Dalgleish]

You could mark the email addresses that should be removed, then filter
for marked rows, and delete the results. These instructions are for
Excel for Windows, but should be similar for the Mac:

Assuming your list of addresses is in Sheet1, column A, and the
'addresses to remove' list is in Sheet2, column A, and Sheet1, row 1
contains headings:

1. In Sheet1, cell B2, enter the following formula:
=IF(COUNTIF(Sheet2!A:A,A2)>0,"X","")
2. Copy the formula down to the last row of data
3. Add a heading in cell B1, e.g. Delete
4. Select a cell in the list on sheet 1.
5. Choose Data>Filter>AutoFilter
6. From the dropdown list in cell B1, choose X
7. Select all the visible rows (marked X for deletion)
8. Choose Edit>Delete Row
9. Choose Data>Filter>AutoFilter, to remove the filter
10. Delete column B.

 

[jason justice]

thanks!
looks like it worked.

-----Original Message-----
You could mark the email addresses that should be removed, then filter
for marked rows, and delete the results. These instructions are for
Excel for Windows, but should be similar for the Mac:

Assuming your list of addresses is in Sheet1, column A, and the
'addresses to remove' list is in Sheet2, column A, and Sheet1, row 1
contains headings:

1. In Sheet1, cell B2, enter the following formula:
=IF(COUNTIF(Sheet2!A:A,A2)>0,"X","")
2. Copy the formula down to the last row of data
3. Add a heading in cell B1, e.g. Delete
4. Select a cell in the list on sheet 1.
5. Choose Data>Filter>AutoFilter
6. From the dropdown list in cell B1, choose X
7. Select all the visible rows (marked X for deletion)
8. Choose Edit>Delete Row
9. Choose Data>Filter>AutoFilter, to remove the filter
10. Delete column B.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.