The proposed formula seems to be working only if the top-left corner of the matrix is in A1. It would be nice to have something that also takes an already existing matrix and sets only the diagonal to 0, leaving all other elements unaltered.
So I put together this improvement:
=arrayformula(if((row(B14:L24)-row(B14)) = (COLUMN(B14:L24)-COLUMN(B14)),0, B14:...
OK it's pretty nasty but I was able to get it done.
Assume the first row we start from is row 2.
The date column is calculated like this with 43831 being 1/1/2020
You copy this field infinitely downwards. What happens here is that each January 1st the date is reset so the next client will stay with the same ...
The following method works, and is not too arcane:
A: I defined 3 named ranges, just to make my formulas easier. This sheet is called 'Fix' so the named ranges use Fix as a prefix.
FixDataToChange is the stuff with the errors in it.
FixRegex (Yellow) are the patterns to use. Most of them are simple text patterns.
FixChangeTo is the material to replace ...