Formula to search row of semicolon separated values against a list

[Atreust]

Hi there,

I have a list of email addresses that are owners of SP sites. Some are single email addresses, some are multiple and separated by a semicolon, and some are blank. I have a list of active personnel in another row (each email address has it's own cell in the row, there are just under 1000) that I'd like to compare to the list of owners. When it matches active personnel, I'd like to delete the row so that we can identify orphaned groups. I had a formula that worked for the cells with only one email address, but it didn't work when there are multiple.

Thanks in advance.

 

[awoohaw]

@Atreust , Welcome to the forum.

You will get a faster response if you provide some examples of your data (anonymized).
Mr. Excel forum has a tool, xl2bb add in (link below) which allows you to copy and paste mini worksheets into a post. If you are unable to do that then you can also post your data as a table.

Thanks in advance for helping the forum help you!

 

[Atreust]

Thank you! I'm looking to add a formula in the Keep/Delete column. If there is one of the Current Personnel (Column C) in the Owners column, then I'd like Column B to read Delete. If the owners column is blank or there are no Current Personnel, then I'd like it to read Keep.

Owners	Keep/Delete	Current Personnel
john@gmail.com; jason@gmail.com; sarah@gmail.com	Delete	jason@gmail.com
matt@hotmail.com	Delete	sarah@gmail.com
	Keep	matt@hotmail.com
sarah@gmail.com	Delete	greg@gmail.com
john@gmail.com	Keep	natalie@hotmail.com
leslie@hotmail.com; jack@gmail.com	Keep

Hopefully this makes sense.

 

[awoohaw]

try this:

Mr excel questions 60.xlsm
	A	B	C	D	E	F
1	Owners			Keep/Delete	Current Personnel
2	john@gmail.com; jason@gmail.com; sarah@gmail.com	Delete		Delete	jason@gmail.com
3	matt@hotmail.com	Delete		Delete	sarah@gmail.com
4		Keep		Keep	matt@hotmail.com
5	sarah@gmail.com	Delete		Delete	greg@gmail.com
6	john@gmail.com	Keep		Keep	natalie@hotmail.com
7	leslie@hotmail.com; jack@gmail.com	Keep		Keep
Atreust

Cell Formulas
Range	Formula
B2:B7	B2	=IF(SUM((--(ISNUMBER(FIND($E$2:$E$6,A2)))))>0,"Delete","Keep")

 

[Atreust]

try this:

Mr excel questions 60.xlsm
	A	B	C	D	E	F
1	Owners			Keep/Delete	Current Personnel
2	john@gmail.com; jason@gmail.com; sarah@gmail.com	Delete		Delete	jason@gmail.com
3	matt@hotmail.com	Delete		Delete	sarah@gmail.com
4		Keep		Keep	matt@hotmail.com
5	sarah@gmail.com	Delete		Delete	greg@gmail.com
6	john@gmail.com	Keep		Keep	natalie@hotmail.com
7	leslie@hotmail.com; jack@gmail.com	Keep		Keep
Atreust

Cell Formulas
Range	Formula
B2:B7	B2	=IF(SUM((--(ISNUMBER(FIND($E$2:$E$6,A2)))))>0,"Delete","Keep")

Thank you very much! This worked aside from one issue. Is there a way to make it so it is not case sensitive? Our email format usually has FirstnameLastname@gmail.com but some in this list are firstnamelastname@gmail.com and they are reading incorrectly.

Thanks again.

 

[awoohaw]

try:

=IF(SUM((--(ISNUMBER(FIND(UPPER($E$2:$E$6),UPPER(A2))))))>0,"Delete","Keep")

 

[Atreust]

try:

=IF(SUM((--(ISNUMBER(FIND(UPPER($E$2:$E$6),UPPER(A2))))))>0,"Delete","Keep")

Perfect, solved. Thank you very very much!

 

[awoohaw]

I am happy you found a solution!
Best Wishes!