Can I suggest that you break the problem down. Here is some sample data that I set up
Row SLA Name Month Met/Not Met
1 Item 1 2022-01 Not Met
2 Item 1 2022-02 Not Met
3 Item 1 2022-03 Not Met
4 Item 2 2022-01 Not Met
5 Item 2 2022-02 Met
6 Item 2 2022-03 Not Met
7 Item 3 2022-01 Met
8 Item 3 2022-02 Not Met
9 Item 3 2022-03 Not Met
10 Item 4 2022-03 Not Met
11 Item 5 2021-12 Not Met
12 Item 5 2022-01 Not Met
13 Item 5 2022-02 Met
14 Item 5 2022-03 Met
From this I would expect the comment '3M' to be added to Row 1 and the comment '2M' to be added to rows 4 and 7. Note that Item 5 has a row for 2021-12 which is out of scope, so that "Not Met" should not be counted.
The first step I did was to add a value in $K$1 as the "oldest" month that is relevant for this analysis -
2022-01
Then I added two "worker" columns "Met as Numeric" and "Number Not Met"
In the first cell in column E "Met as Numeric" I placed the formula
=IF(AND(C2="Not Met",B2 >=$K$1),1,0)
Note the dollar signs on $K$1 so that it doesn't change as I auto-fill the column. Highlight the cell and then hit Ctrl-DownArrow until that column is highlighted for all your data rows. Use Data, Flash Fill (
Using Flash Fill in Excel[
^]) to copy the formula down to the end of your data.
This took me approximately 20 seconds to process 1,000,000 records. Now column E contains a 1 if the SLA was Not Met and the Month is our control month or later. You may have to change this depending on how you are storing "Month". I have assumed it is YYYY-MM
In the second worker column F "Number Not Met" I placed the formula
=SUMIF(A:A, A2,E:E)
Flash fill as above, this took longer, approximately 60 seconds. How long it takes for you will depend on your machine set up. You can speed up this process by setting the Formulas, Calculation Mode to "Manual". Remember to set it back to Automatic before exiting Excel.
Now column F contains the number of "Not Met" entries for each [SLA Name] within the last 3 months.
Finally in the Comment column I added this formula
=IF(AND(F2=3,B2=$K$1),"3M",IF(AND(F2=2,B2=$K$1),"2M",""))
The reason for checking back to the control date is so that only the "first" row for each item is updated.
These will still take some time to update but keeping the formulae simple, and avoiding the use of User functions should make it achievable.
An alternative you might want to consider is using Power Query - I'm not sure that you would gain any performance gains, however you would end up with an easily repeatable process if this is to be an on-going requirement
Submit an article or tip