VBA to search list for 2 account variables, then calculate a new account variable

[tallboyz]

Hello. I have an excel worksheet "Data" tab with a list containing the following:

Column A = Department
Column B = Region
Column C = Account Variables (Total Salary by position and Headcount by position)
Column D to U = Monthly totals

I'm looking for a VBA code that will cycle through the data list finding Total Salary by position and Headcount by position at the same Department and Region intersections and calculate "Average Salary" by position for each month. The Average Salary by position for each month = (Total Salary by Position/Headcount by Position/12) and can be added at the bottom of the list or to a separate worksheet called "AvgSalaryOutput".

Any help that you can provide would be greatly appreciated. Thank you

[vba_php]

can you relate the following statement of yours to the actual data in your worksheet? as in, what data = total salary by position, and what data = total headcount by position?

The Average Salary by position for each month = (Total Salary by Position/Total Headcount by Position/12)

from your worksheet data, it really isn't clear what is what. maybe someone else sees it differently.

[tallboyz]

Hi. Thanks for the quick response. Sorry for the confusion as I included the word "Total" in front of "headcount" and the actual account variable name does not show it that way. Also, the formula for Average Salary would need an iferror statement to handle errors in the formula if there is zero headcount or zero total salary reported for a particular month. Where the Department and Region intersections are the same, the Average Salary for each month is:

= iferror(Total Salary by Position/Headcount by Position/12,0)

As an example, the Average Salary for the R&D, APAC, 5X position for the month of January 2019 would be:

=iferror(Total Salary - 5X/Headcount - 5X/ 12,0)
=iferror(237081/1/12,0) = 19757

I have color coded examples in the screenshot to clarify the calculation:
Attachment 691550

[vba_php]

I have color coded examples in the screenshot to clarify the calculation:
Attachment 691550

I have yet to figure out how you, and many others here, upload images the way you did. for some strange reason I cannot view them when clicking on the link. can you upload the image another way?

[tallboyz]

Hi. I'm still new to this board and am not familiar with how to attach documents or images. I'll try attaching it to the post. See attached for the example image and workbook. Let me know if this works.

[vba_php]

try this formula in a cell first and see if you get the result desired. you should:

Please Login or Register  to view this content.

after that, code will obviously needed to finish it.

[tallboyz]

Which cell should I place that formula using the example workbook I attached? The formula as is does not yield the desired result. Cells A26:U35 in the example workbook "Data" tab shows the desired output that I'm looking for.

Essentially, the VBA would need to create the "Average Salary" account variable by position, copy the Department and Region intersection description into the adjacent cells, then calculate the Average Salary amount by month.

I can do this using a calculated field in a pivot table, but would prefer an automated solution via VBA as this template will be used by multiple users who will have different datasets of Total Salary and Headcount by position that may change frequently.

[vba_php]

Which cell should I place that formula using the example workbook I attached? The formula as is does not yield the desired result. Cells A26:U35 in the example workbook "Data" tab shows the desired output that I'm looking for.

Essentially, the VBA would need to create the "Average Salary" account variable by position, copy the Department and Region intersection description into the adjacent cells, then calculate the Average Salary amount by month.

I can do this using a calculated field in a pivot table, but would prefer an automated solution via VBA as this template will be used by multiple users who will have different datasets of Total Salary and Headcount by position that may change frequently.

you already have the formulas that you need at the bottom of the sheet named data. so essentially all you need to do then is forget about the vlookup() i wrote and loop through everything. I know there are easier ways to do most of the automation that I have come up with below because I mostly write patterns all the time, but if you want to do it the way I have written it, go ahead:

Please Login or Register  to view this content.

the code does not actually execute though, and I tried to look up the answer to this nonsense but could not find it anywhere. this line of code:

Please Login or Register  to view this content.

throws an overflow error the first time it encounters a ''Total Salary'' value of ''0''. I have no idea what the issue could be, other than how you have those cells formatted. You have it set to custom, and the data mask is huge. I would recommend not doing that. Personall, I have never used a custom data mask in Excel ever in my lifetime. Furthermore, I know the code works because if you change the offending line to the following (or any literal number) it works just fine:

Please Login or Register  to view this content.

so....obviously all this code will satisfy the workbook you uploaded. You will have to change it to suit your needs with the actual workbook you have or will be working with in the future.

One of the other reasons I wrote the code this way is to illustrate how a lot of business software is written. And that is to say, very cheaply. At the bottom of this post is a great example of cheap database software that was bought by the biggest website hosting company in the world. Notice the huge number of iterations of the same SQL statement, line after line, as a confirmation by the program to execute a ONE LINE query. Interesting isn't it? So, I hope the code provides some value to you. Good luck with it!

[jindon]

For the data uploaded

Please Login or Register  to view this content.

[vba_php]

LOL. there's a classic example of a manager and a programmer doing the exact same thing. so funny.

[tallboyz]

Hi. Thank you so much for looking into this! This code is brilliant and works perfectly for the example workbook. I am running into issues however when adding more Headcount and Total Salary data to the list and the job position (job title) is not 3 characters long ending in "- #X". The job position is actually all text after the first instance of "- ". For example:

Headcount - PhD Scientist 5X
Total Salary - PhD Scientist 5X
Headcount - Non-PhD Scientist 6Z
Total Salary - Non-PhD Scientist 6Z

In these examples, the job positions are "PhD Scientist 5X" and "Non-PhD Scientist 6Z". How do I re-write the code with this in mind?

[jindon]

Need to see your workbook and the result that you want.

[tallboyz]

Hi. Attached is the example workbook. The "Desired AvgSalaryOutput" tab has the desired example results. Your code works perfectly only if the job positions in Column C of the Data tab end with "- #X". However, job positions can have multiple "-" and do not always end with "- #X". Job positions should be all text values after the first instance of "- " in column C of Data tab. For example:

"Headcount - PhD-Scientist 5X" Job position = PhD-Scientist 5X

[jindon]

1) Don't send PM notice when you post. My mail box get full.
2) As your data in col.C are completely different from the one before, and also if your DESIRED result is just like that much number of rows, I don't think I understand the logic.

[tallboyz]

Hi. Apologies for the confusion. This is my first time using a help forum and am not sure if I am explaining clearly and correctly. The desired output tab that I put in the workbook is just an example showing the results if the average salary macro were to calculate correctly for the customer service department intersections from the “data” tab.

What I am trying to explain is that the code you wrote in the workbook only works if the information in column C of the data tab follows a specific naming format. I put a fresh set of data with more job position information in column C of the data tab, and your code failed. However, when I copied the original information from the “old test data” tab into the “data“ tab, your code worked perfectly. I just need to modify your code so that it recognizes variations of job position names in column c of data tab.

1) Don't send PM notice when you post. My mail box get full.
2) As your data in col.C are completely different from the one before, and also if your DESIRED result is just like that much number of rows, I don't think I understand the logic.

[jindon]

Of course, it was specially tailored to the first workbook that you uploaded.

Now col.C changed from
Total Salary - 5X
Total Salary - 6X
Total Salary - 7X

to
Avg Salary - Call Center 5V
Avg Salary - Call Center 6V
Avg Salary - Call Center 7V

Also irregualr case like in row 26
Headcount - Unpaid

Since your desired result showing a few rows, I can not test my result to yours.

[jindon]

You should be aware that you are the only one who knows the result that you want, so you should explain the logic as clear as possible.

And also, when you upload a sample workbook, sheet layouts and the key column data MUST follow the pattern to your true data,
otherwise, it needs to be re-built from the all scratch again sometime.

Unlike formula system, adjusting vba code is sometime really hard.

Here's my guess.
I'm going out so next reply will be late.

Please Login or Register  to view this content.

[tallboyz]

Hi,
Thanks again for your help! Your new code works, but for some reason it does not correctly calculate average salary for test job positions that have number "7" in the description. For example, these job positions containing number "7" were not calculated correctly:

Average Salary - Field 7T
Average Salary - Field 7V
Average Salary - Call Center 7T
Average Salary - Call Center 7V
Average Salary - Scientist 7T
Average Salary - PhD-Scientist 7T
Average Salary - Scientist 7Z
Average Salary - PhD-Scientist 7Z


Average Salary for all other alphanumerical job positions were calculated perfectly! Attached is the workbook that compares the desired results against the test results. Do you think it is just a minor correction?

[jindon]

Try

Please Login or Register  to view this content.

[tallboyz]

Thank you so much for your help with this! The modifications you made to the code worked, and the average salary calculation was executed successfully, without any errors. Thanks again!

[tallboyz]

Hello,

I just noticed another issue with the VBA Script. The script does not consolidate and summarize duplicate rows before calculating monthly Average Salary {by Organization, Region, and Salary Band Level}
= iferror(Total Salary/Headcount/12,0). Instead the script treats duplicate rows as a separate data group.

In the attached example, R&D and Government groups use the same Region and Salary Bands. At this level, the VBA script correctly calculates Average Salary for R&D and Government as separate Organizations
even though they use the same Region and Salary Bands.

In the same attached example, I replace R&D and Government groups with the parent Organization they rollup to "Clinical Studies - Science | CC.1234.71234". They still use the same Region and Salary Bands.
At this level, the VBA Script should summarize the data for Clinical Studies - Science | CC.1234.71234 before calculating average salary. Instead, the script treats the data as separate R&D and Government
levels.

Attached is the test workbook along with the desired output in the DesiredResultLvl2 tab. The current script being used is also included.
Thanks again for all of your help with this script!

Please Login or Register  to view this content.

[jindon]

I don't understand what you are asking now.
The code is consolidating based on a concatenation of Col.A, Col.B & Col.C as "key".

In the same attached example, I replace R&D and Government groups with the parent Organization they rollup to "Clinical Studies - Science | CC.1234.71234". They still use the same Region and Salary Bands.
At this level, the VBA Script should summarize the data for Clinical Studies - Science | CC.1234.71234 before calculating average salary. Instead, the script treats the data as separate R&D and Government
levels.

[tallboyz]

Hi. Please see the TestResultLvl2 tab for the output. Notice how the calculation treats the average salary calculation as a separate set for the Organization "Clinical Studies - Science | CC.1234.71234", and Region "Domestic". There should only be one set in the result (see the desired output tab). The code is not consolidating column A, B, C in the TestDataLvl2 tab before it calculates the average salary.

[jindon]

row 24

Your desired results are

Please Login or Register  to view this content.

Why Mar & Apr differs from Jan & Feb, they are in the same "YEAR".

[tallboyz]

Hi. My desired results are in columns A:U of the "DesiredResultLvl2" tab. You are looking at the variance vs what the code is calculating.

AvgSal Example 4 - Copy.PNG

[jindon]

It Is from "DesiredResultLvl2" tab.

Are we talking the same workbook?

[tallboyz]

Correct. It is the same workbook. The desired results are in column A:U of the "DesiredResultLvl2" tab.

The variance vs the code is calculated in columns W:AN of this same tab and are highlighted in orange color

[jindon]

So, again, why COL.E & COL.F are different from COL.C & COL.D on row 24 of "DesiredResultLvl2" tab?

[tallboyz]

It is ok for the monthly average salary output on row 24 of the "DesiredResultLvl2" tab to be different. See attached screenshot. I highlighted few other examples where the monthly output on various rows are different. This is ok.

Essentially what the data is showing is a "real world" case where employees at the same level in the organization are paid differently. In addition, each month the organization may have turnover or new employees. This changes the
weighted mix of pay scales of those employees at that same level. This is entirely normal and why we trend the average salary over time to assess the market pay rates.

AvgSal Example 5 - Copy.PNG

[jindon]

OK, I though it was 1 to 1 relation ship...

Take the data in col.C ends with "Call Center 5Z" from "TestDataLvl2".

There are rows, 4, 8, 18 & 33 where 4 & 18 Is "Total salary" and "Headcount" for the other.

How should it be calculated?

[jindon]

It was not as good sample...

Take the data ends "Scientist 7V"
41,54,99.113,159 & 172 are same unique in Col.A, B & C

[tallboyz]

The data in column C is the employee level or salary band. Notice rows 4 and 8 share the same Organization and "Domestic" Region. Rows 18, and 33 are for the Central America region

A Summifs formula may work well for the calculation as each column should be consolidated to find the unique combination of Column A,B and C Total Salary intersections that is
divided by the unique combination of Column A,B, and C Headcount intersections before calculating the average salary as follows:

iferror(Total Salary - Call Center 5Z / Headcount - Call Center 5Z / 12,0)

AvgSal Example 6 - Copy.PNG

[jindon]

Did you see my previous post?