Criteria based Addition and Subtraction - VBA Request

Good day everyone!

I need some help on a piece of analysis that has been taking a lot of time of my daily work. I’ve provided the explanation and expected output in the tab Named Report. My data is huge, and therefore; I’m requesting some assistance with a Macro that can help me accomplish this. Here’s my request in a nutshell.

The process:

Calculate % for each team member to provide benefit for entries tagged as “Not Found” in column B and “Negative” in column C of the Tab named “DATA”
The % benefit is to be calculated from Column E of Tab 1, and added to the total Positive Count, and at the same time excluded from the total Negative Count
The logic is to also help me update the change in status for each entry in the Data tab.

Priority on converting Negatives to Positives under each category:

Priority 1: AD
Priority 2: WP
Priority 3: AT
Priority 4: VM

Examples:

1:If there are 4 Not Found + Negatives: The Negatives should be equally divided and subtracted from each Negative category, and the benefit is added to Positive (In this case: 1 Not Found Negative will be subtracted from LD, WP, AT and VM)
2:If there are 3 Not Found + Negatives: The Negatives should be equally divided and subtracted from each Negative category in order of Priority listed above, and the benefit is added to Positive (In this case: 1 Not Found Negative will be subtracted from LD, WP and AT)
3: If there are 5 Not Found + Negatives: The Negatives should be divided and subtracted from each Negative category, and the benefit is added to Positive in order of Priority (In this case: Not Found Negative will be subtracted as follows 2 LD, 1 WP, 1 AT and 1 VM)

The same logic is to be run on 60%, 70% as well. I was hoping for a dynamic dropdown or something wherein I can easily change the % allocation based on the requirement.

Please help me counter this. Thanks a lot!

Happy New Year!

Best,

Greetings All!

Could you please assist me on this request.

Thank you!

Best regards,
Spi

Dearest Members,

May I please request your kind help on this analytics.

Please let me know if you need any clarification or details. I'll be more than happy to provide more clarity on this request.

Look forward to your kind assistance!

Thanks in advance.

Kindest regards,
Spi

Hi,

I believe my request – in its current explanation in post 1 - turned out to be a bit ambiguous. So here’s a step-by-step explanation of the request using a “Single” example. I’ve pro-actively added a data sheet for better understanding. Hope this will make it a bit clear. Apologies for making it a little confusing initially!

Here it goes….

There are 2 tabs in my spreadsheet namely: (1) Report & (2) DATA

Step 1: Identify the unique “Client Names” from “DATA” tab Column A (Cell A2 onwards), and paste the unique listing in Column A (Cell A3 onwards) in the tab named “Report”
Step 2: Count the rows tagged as “Positive” by each Client Name from Column C of the “DATA” tab, and update the count in Column B (Cell B3 onwards) in the tab named “Report”
Step 3: Count the rows tagged as “Negative” by each Client Name from Column C of the “DATA” tab, and update the count in Column C (Cell C3 onwards) “Report”
Step 4: In column D (Cell D3 onwards) of the tab named “Report” update the counts of rows by Client Name that meet the following combination “Found” in Column B and “Negative” in Column C of the tab named “DATA”
Step 5: In column E (Cell E3 onwards) of the tab named “Report” update the counts of rows by Client Name that meet the following combination “Not Found” in Column B and “Negative” in Column C of the tab named “DATA”
Step 6: In the tab named “Report” Column F (Cell F3 onwards) calculate 80% of values in Column E.
Step 7: The outcome; i.e. 80% of the combination from Column E “Not Found & Negative” is to be added to the “Positive” count and subtracted from “Negative” count. The new values are to be pasted by client in column G (Cell G3 onwards) and Column H (Cell H3 onwards) of the “Report” tab
Step 8: The 80% logic applied is to also create a log identifying the changes to the original status “Positive/Negative” in column C of the “DATA” tab. The updated status based on the resultant change is to get updated in Column E of the “DATA” tab.

Let’s take an example:

Column A (cell A3) - Client Name AG
Column B (cell B3) - Positive Count: 157
Column C (cell C3) - Negative Count: 30
Column D (cell D3) - Combination of Found (Column B) and Negative (Column C) of the DATA tab: 20
Column E (cell E3) - Combination of Not Found (Column B) and Negative (Column C) of the DATA tab: 10
Column F (cell F3) - 80% of Column E of the Report tab: 8
Column G (cell G3) - New Positive Count: 165 {157 Original Positive + 8 (80% of 10)}
Column H (cell H3) - New Negative Count: 22 {30 Original Negative - 8 (80% of 10)}

Report Tab Completed

Changes required in “DATA” tab (description per Step 8 above). Explanation of the requirement:

This requires me to modify the status of column C in the DATA tab, and present the outcome in column E after applying the 80% benefit. The resultant change in the status is based on the “Category”. To elaborate:

In case of client AG, 80% of column E {Not Found (Column B) and Negative (Column C)} of the DATA tab = 8, but this 8 is split between different categories in Column D of the of the DATA tab. I need to ensure that these 8 Not Found and Negatives are changed as such that they are converted from each category, and not in a manner that all the deductions happen from a single category.

Example:

Client AG has 10 Not Found (Column B) and Negatives (Column C) in total.
Category Split: AD = 6, WP = 1, AT = 3, and VM = 0
80% of these 10 Not Found (Column B) and Negatives (Column C) amount to 8
Deduction Priority is to be such that I need to subtract one by one from each category as long as they are positive and append updated Status as “Positive” in Column E for each row item. The priority for changing is as follows:

8 Not Found (Column B) and Negatives (Column C) to be deducted and status to be updated as highlighted in Column E of the Data tab
Priority 1: AD (6-1-1-1-1)
Priority 2: WP (1-1)
Priority 3: AT (3-1-1-1)
Priority 4: VM (0-0)

Hope this makes it more clear. Sorry about too much text, but I think it was need of the hour. Happy to explain it further.

Thanks much!

Best regards,
Spi

Thanks a billion Lewis. This is truly magical. The script runs smoothly with the dummy data.

I'm now updating the script with my real data labels. I hope I'm able to do that perfectly, and it doesn't cause the script to experience any errors. But with my limited VBA know-how, I just hope that I'm able to accomplish it correctly. I hope it'll be okay to comeback with any questions that I may have or need further assistance.

I'll get back to you with another "Thanking" email shortly.

Best,
Spi

Dear Lewis,

Your Script is beautiful. I have a question. To give you a brief, here's what I've done:

Changed VM to Voicemail (my real lable) while testing in the script on these 3 locations:

wsReport.Cells(iReportRow, "F") = "VM"
wsReport.Cells(iReportRow, "L") = "VM"
Public Const sCategoryPriorityList = "AD,WP,AT,VM"

But am getting a run time error 9 script out of range on this line:

iNegativeNotFoundCountForCategory = myCategoryTypeArray(iCategoryArrayIndex).iNegativeNotFoundCountForCategoryOriginal

Could you please assist me if am doing something incorrectly.

Thank you!

Originally Posted by LJMetzger

Hi Spi,

Thank you for catching a very serious software error, for a situation that I overlooked.
The runtime should not occur and a warning message will be displayed in the report.

The error occurred because items in the 'Priority List' didn't match Items in Sheet 'Data'.



There is a 4th change that is needed.
The 'VM' values in Sheet Data Column 'D' (Category) have to be changed to 'Voicemail'.

See the attached file which should fix the problem. Sorry for the inconvenience.

Lewis

Dear Lewis,

Thanks a lot for this, but am still not able to run it successfully. I'm sure its to do with my limited experience.

I've changed the labels as per my original coding coming from the system in all the 4 places, and also replaced the words Negative and Positive with my original coding. However, on running the script the following occurs:

DATA INTEGRITY ERROR - BAD 'Priority List' or BAD Data in Sheet 'DATA' Column 'D' (Category).

I've attached the software with the changes I've incorporated for your easy reference. Could you please take a look and advise where am going wrong.

Thank you for your patience, understanding and help!

To summarize: I changed the following in the script:

Positive = GT
Negative = BT
Priority 1: AD = Agent Delay
Priority 2: WP = Wrong Person
Priority 3: AT = Admin Transfer
Priority 4: VM = Voicemail



Best regards,
Spi

Originally Posted by LJMetzger

Hi Spi,

Try the new attached file. I think I fixed the problems.

Lewis

You are a gem! Thanks for such a detailed explanation. My bad for not realizing that I may end up violating some rules!

I am playing with the data to see if this works as we (you and I) have been anticipating it to. I encountered the “DATA INTEGRITY ERROR - BAD 'Priority List' or BAD Data in Sheet 'DATA' Column 'D' (Category)” error in my tests again today. I’m not sure why this may be happening. Could you please take a look at the attached and advise.

I’m sorry for the to-and-fro, but I really want to test it as thoroughly as I can.

Thank you for all the time and effort you are putting in for me. I’m obliged.

Regards,
Spi

Originally Posted by LJMetzger

There is no need to apologize. You did an excellent job testing, and found another unwanted feature in my mode. I never anticipated a case where there would be NO NEGATIVES to reduce. The code generated an error where there never should have been an error.

Software is only as good as the test protocols used to verify the software.

Great job Spi. Try the attached file.

Lewis

Thanks much Lewis.

I'm running the tests now, and will keep you posted on the progress. That said, I can't thank you enough for your help. I'm blessed to have come across a person like you. A true gem!

- I have 2 requests, which we can obviously look into (if you agree) once I've completed testing.

1). How difficult would it be to move the "BT and Not Found Analysis" tables to a different tab? It will immensely help as my data is huge and will avoid any unwarranted issues.
2). Would it be possible to add columns to give the 60% and 70% results as it is currently doing for 80% in Report tab Row 2; i.e. from column I to L?

Thanks,

Spi

Hi Lewis,

I’ve been running the tests all night, and have the following observations on the software. Could you please take a look.

The code is not counting all the entries. To cite few examples:

User “A . P46” (font highlighted in red), the total GT count is 256 in the “Data” tab, but in the “Report” tab the count getting updated in column B (Cell B4) is only 2. The same thing is happening in case of BT, the original count should be 17, but it is getting updated as 1 only in the “Report” tab (Ref: Cell C4).
User “A Ano29”, the original GT count is 31 in the “Data” tab, but it is only counting it as 8 (Ref: Cell B5). The BT count is 2 in “Data” tab, but it is only counting it as 1 (Ref: Cell C5) in the report tab, which is also affecting the conversions from BT to GT in the Data tab (Column E). In this case, 80% of 2 BTs = 1.6 so both the BTs (Row 15 and Row 16 Cell) should be converted into GTs, but that’s not happening; i.e. only one is getting updated
User “A Dar43”, the original GT count per Data tab should be 233, Cell B3 of “Report “ tab shows it as 7. BT count in this case should be 15, the “Report” tab shows it as 1 (Ref: Cell C3).
Apart from these I’m still facing “DATA INTEGRITY ERROR - BAD 'Priority List' or BAD Data in Sheet 'DATA' Column 'D' (Category).” Errors. Please see Row 687 as an example, and there are few more.

It looks like all of the above is impacting the conversions. I’ve attached the sheet with data. The 3 examples stated above have been isolated in Red font for easy reference.

Please let me know if you have an questions or in case I’ve confused things in anyway.

Best,
Spi

Originally Posted by LJMetzger

Hi Spi,

It will probably take a few days, but I'm working on the items in your 2 most recent posts. I will put a priority on fixing the errors.

Lewis

Dear Lewis,

Please take your time. I can't ask for more

You've been extremely helpful and paitent with me. Thanks a bunch!

Regards,
Spi

Originally Posted by LJMetzger

Hi Spi,

I apologize for the delay but it was unavoidable. See the attached file which contains:
a. Your completed enhancement requests.
b. Correction of the 'Count Errors'. The error has been around since the first version, but only manifests itself when all the items in a single category are not contiguous (i.e. together).
c. Addition of code to help diagnose REAL errors caused by 'BAD CATEGORIES' in the 'Not Found' Column.

Lewis

No need to apologies Lewis. Thanks a billion for this. I'll run my tests and comeback to close the thread / seek any further assistance (if required). Please give me 2-3 days.

Thanks again

Best,
Spi

Originally Posted by LJMetzger

Hi Spi,

I apologize for the delay but it was unavoidable. See the attached file which contains:
a. Your completed enhancement requests.
b. Correction of the 'Count Errors'. The error has been around since the first version, but only manifests itself when all the items in a single category are not contiguous (i.e. together).
c. Addition of code to help diagnose REAL errors caused by 'BAD CATEGORIES' in the 'Not Found' Column.

Lewis

Dear Lewis,

Your solution is magical. I apologies for the delay, but had been testing it thoroughly. All my tests have come out good. Thanks for your efforts. You truly are a genius

Thanks a billion. You've made my life simple!

Regards,
Spi