Formula used in Excel 2016 no longer working in Excel 365

Hello Everyone. I am hoping someone can help. My company updated Office from 2016 to 365. I am noticing that some Excel formulas are not working, and also taking a lot longer to run. Here is one that previously worked in Excel 2016 but causing 365 to freeze:

- Deleted -

Is there a better, updated formula for the above? If not is there something I need to do to get the above formula to run?

Try using defined instead of full column ranges. Limiting them to something like 5000 rows is usually enough for most situations.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

There's nothing at all in that formula that >>>SHOULD<<< cause any performance issues when migrating from 2016 to O365. If you want future responses here to focus on O365, please amend your profile, which now may be out of date (it shows 2016).

Again, a sample file might help.

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.