Identifying companies with multiple email domains

[DaRTH KiRo]

I have company IDs listed out and repeated in column A, and in column M, I have email domains. Some email domains may be repeated but some companies may have multiple domains for a single company ID. I need to identify the company IDs in column A that have multiple email domains in column M. In the end, I need to identify and remove additional company email domains so that we only have one domain per company. I was unable to come up with anything useful when googling so I was curious if you all could help.

 

[EXCEL MAX]

Hello DARTH KiRo,
try this...
Insert this formula "=COUNTIFS(A2:$A$10000;A2)" to "N2", set number of max rows in the formula, fill down rows.
After that apply filter over column "N" with criteria "Greater then 1" and delete filtered rows.
In a case you have duplicate ID, apply "Remove duplicates".

 

[DaRTH KiRo]

Hello DARTH KiRo,
try this...
Insert this formula "=COUNTIFS(A2:$A$10000;A2)" to "N2", set number of max rows in the formula, fill down rows.
After that apply filter over column "N" with criteria "Greater then 1" and delete filtered rows.
In a case you have duplicate ID, apply "Remove duplicates".

Each line designates an email address, from column I, and I do need all of those so I cannot remove duplicates. I just need to identify companies with more than one domain name. Please see my example below. I would want Company2 to be highlighted so I know there is more than one domain name for that company.

Column A	Column I	Column M
Company1	AB@company1.com	company1.com
Company1	BC@company1.com	company1.com
Company1	CD@company1.com	company1.com
Company2	DE@company2.com	company2.com
Company2	EF@difcompany.com	difcompany.com
Company3	FG@company3.com	company3.com

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column A								Column I				Column M
2	Company1								AB@company1.com				company1.com
3	Company1								BC@company1.com				company1.com
4	Company1								CD@company1.com				company1.com
5	Company2								DE@company2.com				company2.com
6	Company2								EF@difcompany.com				difcompany.com
7	Company3								FG@company3.com				company3.com
8
9
10
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A10	Expression	=COUNTIFS($A$2:$A$10,A2,$M$2:$M$10,"<>"&M2)>0	text	NO

 

[DaRTH KiRo]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column A								Column I				Column M
2	Company1								AB@company1.com				company1.com
3	Company1								BC@company1.com				company1.com
4	Company1								CD@company1.com				company1.com
5	Company2								DE@company2.com				company2.com
6	Company2								EF@difcompany.com				difcompany.com
7	Company3								FG@company3.com				company3.com
8
9
10
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A10	Expression	=COUNTIFS($A$2:$A$10,A2,$M$2:$M$10,"<>"&M2)>0	text	NO

Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[DaRTH KiRo]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column A								Column I				Column M
2	Company1								AB@company1.com				company1.com
3	Company1								BC@company1.com				company1.com
4	Company1								CD@company1.com				company1.com
5	Company2								DE@company2.com				company2.com
6	Company2								EF@difcompany.com				difcompany.com
7	Company3								FG@company3.com				company3.com
8
9
10
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A10	Expression	=COUNTIFS($A$2:$A$10,A2,$M$2:$M$10,"<>"&M2)>0	text	NO

I have been toying around with NOT(ISBLANK) to add to the expression you gave me so that it does not count blank cells in column M. I have not had luck, I tried adding it to the front but it only highlights everything and when I add around M range it errors. Is this something that is possible?

 

[Fluff]

How about

=AND(M2<>"",COUNTIFS($A$2:$A$10,A2,$M$2:$M$10,"<>"&M2,$M$2:$M$10,"<>")>0)

 

[DaRTH KiRo]

How about

=AND(M2<>"",COUNTIFS($A$2:$A$10,A2,$M$2:$M$10,"<>"&M2,$M$2:$M$10,"<>")>0)

Perfect, you're the best!

 

[Fluff]

Glad to help & thanks for the feedback.