Highlight duplicates when part of the cell matches in Google Sheets-CodePudding

I have searched as much as I can, and I have found solutions for similar problems, but I haven't been able to find a solution to my exact problem.

Issue: I would like to highlight the row when one cell in column A of that row is an exact match for another cell in that column, AND part of another cell in column B of that row is a match for part of another cell in that column, in Google Sheets. I would like to use conditional formatting, and only highlight the second occurence and on.

For example, is this "sheet":

 A          B             C
1|John Smith|[email protected]|Test Co.
2|Jane Doe  |[email protected]   |X Company
3|John Smith|[email protected] |Test Inc.
4|John Smith|[email protected] |Test Incorporated

I would like row 3 and row 4 to highlight, because column A3 is a duplicate of A1, and everything in B3 after @ matches everything in B1 after @, and the same is true of row 4. Also, only rows 3 and 4 should highlight; not row 1, since it is the first instance. I understand regexes, and I've found how to highlight a row if one cell in column A and one cell in column B is an exact match with other cells is their respective columns, but I haven't figured out how to combine the two where I can search for one cell that is an exact match with another cell in that column AND for one cell that is a partial match with another cell in that particular column. Here is a link to a test sheet that contains the sample info from above.

CodePudding user response：

Try the following custom formula applied to A1:C:

=index((countif($A$1:$A1,$A1)>1)*
(countif(regexextract($B$1:$B1,"@(.*)"),
regexextract($B1,"@(.*)"))>1))