Click here to Skip to main content
15,561,817 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All,

I have been given a data set in Excel which is consist of 20k+ rows.
The data is for the past six months' SLA for 3000+ processes.

Within the last 3 months, I need to identify the processes which have failed to meet their SLAs. Based on the below, the raw with the latest month needs to be updated with a comment (a new column should get updated).

If all 3 months' SLA are Not Met - Comment is "3M"
If any of the 2 months' SLAs are Not Met - Comment is "2M"

SLA Name is on Column A
Month is on Column B
Met/ Not Met is on Column C
I need the comment on Column D

Pardon my Grammar and I'd really appreciate your help which would immensely save my time.

What I have tried:

I first tried a function, but it takes ages to process and then my file is crashing.
I haven't tried any Macros yet as I'm very new to this. I checked about 50+ results from googling, but none of them helped me.
Posted
Updated 18-Mar-22 7:10am
Comments
Maciej Los 16-Mar-22 13:46pm    
We cannot help you without seeing sample data...
Luc Pattyn 16-Mar-22 18:39pm    
Hint: whatever you do, first create a sample data set (a small Excel file), a screenful would be enough, and use that until you get correct operation.

Only then, try the same code with real data; if necessary start looking for optimizations.

1 solution

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
 
Share this answer
 
Comments
Maciej Los 18-Mar-22 17:08pm    
Caroline, i really appreciate your great answer. Excellent job!
CHill60 18-Mar-22 17:10pm    
Thank you! That means a lot given your own expertise in the topic
Maciej Los 18-Mar-22 17:25pm    
Thank you. I wasn't able to understand what OP wanted to achieve. And... i was lazy to reproduce the issue :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900