Delete common between column X and Y, from column X

%3CLINGO-SUB%20id%3D%22lingo-sub-3417859%22%20slang%3D%22en-US%22%3EDelete%20common%20between%20column%20X%20and%20Y%2C%20from%20column%20X%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3417859%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community!%20I%20have%20what%20I%20believe%20to%20be%20a%20simple%20request%2C%20though%20not%20simple%20enough%20for%20myself%20to%20have%20figured%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20of%20contacts%2C%20some%20of%20those%20contacts%20have%20multiple%20email%20addresses%20assigned%20to%20them%20in%20the%20column%20%22Email%20Address%22%2C%20separated%20by%20comma.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20spreadsheet%2C%20I%20also%20have%20a%20column%20named%20%22Unverified%20Addresses%22.%20If%20there%20are%20any%20email%20addresses%20in%20the%26nbsp%3B%22Email%20Address%22%20column%20which%20are%20also%20in%20the%26nbsp%3B%22Unverified%20Addresses%22%20column%2C%20I%20want%20them%20deleted%20from%20the%20%22Email%20Address%22%20column.%20I%20don't%20however%20want%20to%20delete%20the%20verified%20addresses%20the%20contacts%20have.%20So%20if%20a%20contact%20has%205%20addresses%2C%20and%20only%20one%20is%20unverified%2C%20I%20want%20to%20keep%20the%20other%20four%20addresses.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3417859%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3418046%22%20slang%3D%22en-US%22%3ERe%3A%20Delete%20common%20between%20column%20X%20and%20Y%2C%20from%20column%20X%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3418046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1399063%22%20target%3D%22_blank%22%3E%40Carlo1825%3C%2FA%3E%26nbsp%3BFirst%20off%2C%20a%20cell%20formula%20can%20NOT%20change%20the%20value%20or%20properties%20of%20another%20cell%2C%20to%20do%20that%20you%20would%20need%20a%20macro%20(VBA).%20What%20I%20would%20suggest%20is%20to%20create%20a%20new%20column%20called%20Verified%20Email%20Address%20and%20then%20use%20a%20formula%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUBSTITUTE(%20%5BEmail%20Address%5D%2C%20%5BUnverified%20Addresses%5D%2C%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ethat%20would%20work%20if%20there%20is%20only%201%20unverified%20address%2C%20but%20it%20sounds%20like%20there%20are%20multiple%20so%20something%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%20%20%20l%2CLEN(%5B%40email%5D)%2C%0A%20%20%20uvList%2CTRIM(MID(SUBSTITUTE(%5B%40unverified%5D%2C%22%2C%22%2CREPT(%22%20%22%2Cl))%2CSEQUENCE(l%2C1%2C1%2Cl)%2Cl))%2C%0A%20%20%20%20eList%2CTRIM(MID(SUBSTITUTE(%5B%40email%5D%2C%22%2C%22%2CREPT(%22%20%22%2Cl))%2CSEQUENCE(l%2C1%2C1%2Cl)%2Cl))%2C%0A%20%20%20%20fList%2CFILTER(eList%2C(eList%26lt%3B%26gt%3B%22%22)*ISERROR(MATCH(eList%2CuvList%2C0))%2C%22%22)%2C%0A%20%20%20%20TEXTJOIN(%22%2C%20%22%2CTRUE%2CfList))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Band%20example%20of%20this%20in%20action%20is%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello Excel Community! I have what I believe to be a simple request, though not simple enough for myself to have figured out. 

 

I have a spreadsheet of contacts, some of those contacts have multiple email addresses assigned to them in the column "Email Address", separated by comma. 

 

In this spreadsheet, I also have a column named "Unverified Addresses". If there are any email addresses in the "Email Address" column which are also in the "Unverified Addresses" column, I want them deleted from the "Email Address" column. I don't however want to delete the verified addresses the contacts have. So if a contact has 5 addresses, and only one is unverified, I want to keep the other four addresses. 

 

How can I do this?

 

Thank you for any help!

1 Reply

@Carlo1825 First off, a cell formula can NOT change the value or properties of another cell, to do that you would need a macro (VBA). What I would suggest is to create a new column called Verified Email Address and then use a formula like:

=SUBSTITUTE( [Email Address], [Unverified Addresses], "")

that would work if there is only 1 unverified address, but it sounds like there are multiple so something like:

=LET(   l,LEN([@email]),
   uvList,TRIM(MID(SUBSTITUTE([@unverified],",",REPT(" ",l)),SEQUENCE(l,1,1,l),l)),
    eList,TRIM(MID(SUBSTITUTE([@email],",",REPT(" ",l)),SEQUENCE(l,1,1,l),l)),
    fList,FILTER(eList,(eList<>"")*ISERROR(MATCH(eList,uvList,0)),""),
    TEXTJOIN(", ",TRUE,fList))

 and example of this in action is attached