Formula used in Excel 2016 no longer working in Excel 365

OK I have attached an example sheet that does not contain real data. The real document has approximately 50,000 rows.

Issue 1 - All email addresses should end in none.com (some are truncated).

Issue 2 - Column K (Hier) is a system where 2 is a regional manager, 1 is a manager, and a 0 is a clerk. There cannot be any 2's present in column K, so they need to be turned into a 1. However all people with a matching column J (Office MGR) cannot cannot contain more than one 1 in column K. So the formula would need to scan column J (Office MGR) and if a 1 already exists, that existing 1 would need to be changed into a 0.

So in the attached example.xlsx, EX2M for column J has Jim Ace which is a 2 and Jonathan Dabble which is a 1. Since they both have the same Office MGR ID, Jim Ace would need to be turned into a 1 (because all existing 2's need to be changed into a 1), but a 1 already exits (Jonathan Dabble), so Jonathan would need to be changed into a 0.