excel. find and remove help....

[Paul11]

is there a way that excel can look through one spreadsheet and find and remove things from another

to explain. i have a spreadsheet of email addresses (spreadsheet 1), and another spreadsheet of email address (spreadsheet 2) that i want to remove from the first spreadsheet

at the moment, i am going through one by one, is there a way to tell exel to find all the email address from spreadsheet 2 and remove them from spreadsheet 1 ?

thank you

 

[House]

quick and ugly way?

Do Vlookup against the table of address you want to delete then filter the results and delete the values that dont return an error (Ie have a matching cell).

Its really more of a database activity though, very simple querry in access

 

[A[L]C]

Put this is the list sheet macros.....dont forget to change test.xlsx to the name of the sheet on which you want to delete the emails from, and list.xlsx to the name of the list sheet.

Its not the best macro in the world, but it should work

if theres no header, change intCurrentCell to 1


Sub macro1()

'Column has a header so start from row 2
intCurrentCell = "2"

strEMail = ActiveSheet.Range("A" & intCurrentCell).Value

Do Until strEMail = ""

Windows("test.xlsx").Activate
Columns("A:A").Select


Selection.Replace What:=strEMail, Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Windows("list.xlsx").Activate
strName = ActiveSheet.Range("A" & intCurrentCell).Value
Loop



End Sub

 

[Paul11]

not really sure i know what im doing , but ill give that a go

thanks

 

[A[L]C]

feel free to contact me on msn

btw Alt + F11 to get in to the vba editor

 

[londonbairn]

Why complicate it with VBA or Vlookups?

Data->Consolidate

Edit: or advanced filter for unique values

 

[A[L]C]

how does that work then

 

[Cobertizo]

Why complicate it with VBA or Vlookups?

Data->Consolidate

Edit: or advanced filter for unique values

I would imagine that those ways may give you a list of unique email addresses, but wouldn't give you a list excluding email addresses from the other sheet, which is what I believe the OP was after...

I personally would just do a VLOOKUP. It's such a quick method that even a more elegant way wouldn't save you any time...

 

[londonbairn]

I would imagine that those ways may give you a list of unique email addresses, but wouldn't give you a list excluding email addresses from the other sheet, which is what I believe the OP was after...

I personally would just do a VLOOKUP. It's such a quick method that even a more elegant way wouldn't save you any time...

If it is purely a list of emails, copying and pasting one list below the other, do an advanced filter to output unique values is far more user friendly and quicker method. And yes it will combine both lists.

If the OP does nto simply need 1 validated list with all unique emails, then VLOOKUP would probably be the next easiest option.

Or if we wanna get silly about it, let's chuck it into Access and do a simple join to pick them out.

Apologies, I use Excel/Access/VBA day in day out for modelling

 

[Cobertizo]

And yes it will combine both lists.

Not sure that's what he's after though:

is there a way to tell exel to find all the email address from spreadsheet 2 and remove them from spreadsheet 1 ?

Maybe I'm reading this wrong, but I understand that the OP wants to exclude the other list, not combine and remove duplicates (which the advanced filter way is excellent for, as you point out).

 

[londonbairn]

Not sure that's what he's after though:

Maybe I'm reading this wrong, but I understand that the OP wants to exclude the other list, not combine and remove duplicates (which the advanced filter way is excellent for, as you point out).

On reading again, you're right sir. This is what happens when you are still working on something on a Friday night

 

[Paul11]

yes, i want to remove the emails on spreadsheet 1 ( that are listed on spreadsheet 2)

thank you for all your replies, i will have a look at all the options tomorrow. ..

 

[A[L]C]

Did you resolve this?

 

[Paul11]

didnt get a chance to do it yet, sorry, had a hectic weekend.

i will try the suggestions in this thread a bit later...

thanks