Solved

Macro to determine eligibility

Posted on 2014-10-19
16
81 Views
Last Modified: 2014-10-20
Hello,

I need a macro to help me streamline a very manual process.  I don't know how to write them, but I know how to used them.

It's Salary Planning season and I have to download a list of employees on a regular basis and determine who's eligible or not.  It's a very manual process with a lot of vlookup tables.  With close to 30k employees, it takes a long time for the formulas to calculate (approx 20 - 30 minutes at times)

Attached is a file with my formulas and all the eligibility rules.

I love to have a macro that will run and give me one column that list if that employee is "Not Eligible"

Column "Y" on my Data table has the employees that are not eligible

Row P1 - Y1 has my eligibility formulas, based on look up ranges.  

Categories that determine eligibility are:
ESG - Employee Subgroup (There are exceptions to this rule, indicated in column P = TRUE, Q = TRUE, W = FALSE)
PA - Personnel Area = FALSE
ORG - Org Unit = FALSE
CC - Cost Center = FALSE
GRD - Grade = FALSE

The rules are also outlined in the eligible tab
GC-SP-Eligibility-Sample.xlsx
0
Comment
Question by:ablove3
  • 9
  • 4
  • 3
16 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40391037
I love to have a macro that will run and give me one column that list if that employee is "Not Eligible"
I'm confused because column Y is a "column that list if that employee is "Not Eligible"" .
0
 

Author Comment

by:ablove3
ID: 40391066
In column X, I concatenated columns P-W and wrote a formula in Column Y, that states, if the concatenation is blank or equal TRUEFALSE, then Blank which means they're eligible.

If the column X had all FALSEs then the verbiage "Not Eligible" populates in column Y

Column Y is basically a guide for the developer to check is their macro is pulling in the "Not Eligibles" correctly.  I welcome if my conclusion is challenged, because I could have possibly missed something or errored.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40391084
I assume then that you want all "Not Eligible" markers to be in column Y. Please give me two or three example of where a currently blank cell in column Y should be "Not Eligible", and the reason it's not eligible.
0
 

Author Comment

by:ablove3
ID: 40391129
Yes, Column why shows who should and should not be eligible.  If you want you can put your results in column Z in order to compare them.  Filter column Why for "Not Eligible" and then look across columns R - W to see what factors are causing them to be ineligible flagged with "FALSE"

Did I answer your question?
0
 

Author Comment

by:ablove3
ID: 40391147
Oh gosh, I've been working too long.  I typed, "Column why", instead of "Column Y".  I'm so embarrassed.

I hope that information was helpful
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40391203
OK let me ask you this: Is there any cell in column Y that should be "Not Eligible" and isn't? If not then I assume you are asking to have VBA code written to replace the formulas. If so then you don't want to do that since the VBA would be a lot slower then the formulas.

I typed, "Column why", instead of "Column Y"
No need to be embarrassed - I've done similar things but note that as long as someone else hasn't yet posted you can edit your previous post.
0
 

Author Comment

by:ablove3
ID: 40391218
Yes, you are correct, I want the VBA code to replace the formulas, the formulas are only there as an aid to show why  the employee was not eligible. As you can see, some were excluded for more than one reason. The tricky part is the ESG employee subgroup exceptions. There are a list of subgroup that are false and some that are true.
For the general population, Trainee - UE is false, but for employees in India it's TRUE
Australia and China also have similar exceptions.  You'll see the exceptions at the top of Column A on the second tab.
I don't want the sheet to have any formulas. I want all the logic to be done in the code.
0
 

Author Comment

by:ablove3
ID: 40391220
I'll remember your tip the next time about correcting a post, thank you.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:ablove3
ID: 40391222
There are cases where employees may need to be pulled out for a particular country like AU & CN. They're normally included, but these two countries exclude them. I think it's the temporary employees, could be the contractual. I shut my computer down, but you'll see the inclusion or exclusions in the formulas.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40392060
I'm sorry but I don't think I can continue with this question because I don't understand why you want to change from formulas to a macro.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40392891
I'm looking into creating a VBA routine to determine eligibility, although I can't predict that it will be significantly faster than you describe in your original post.   However, there may be some logic issues that might help speed things up.

For example I note that the values in columns P and Q (AU & IN, IN) are not mutually-exclusive with column R (ESG).  That is, if either of the first columns is TRUE, then ESG is always FALSE.  This yields, "TRUEFALSE" in your test column X, which, in turn, means the employee is Eligible.  

In this example you only need to test to see if either P or Q is TRUE to determine if eligible.  

Therefore, you can eliminate column X completely from the worksheet by using this instead in column Y:
=IF(OR(P3,Q3,(R3&S3&T3&U3&V3&W3)=""),"","Not Eligible")


Regards,
-Glenn
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
ID: 40392925
Another formula that could be simplified is the one in column P:
from
=IF(OR(AND(E3="Contractual",G3="IN"),AND(E3="Contractual",G3="AU")),"TRUE","")
to
=IF(AND(E401="Contractual",OR(G401={"IN","AU"})),"TRUE","")

-Glenn
0
 

Author Comment

by:ablove3
ID: 40393508
Hi Martin, thanks for trying.

Glenn I tried your simplified formulas and that speed things up tremendously.  That made a big difference.

Thank you, thank you, thank you!!!
0
 

Author Closing Comment

by:ablove3
ID: 40393511
I asked for a macro and the programmers felt that it wouldn't speed my process up much, but Clenn offered more simplied formulas and they made a big difference.  Thank you for the effort Glenn!!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40393538
You can also update the formula in column W (similar to the logic checks in column P):
=IF(AND(E3="Temporary",OR(G3={"AU","CN"})),"N","")

Glad I could help.

-Glenn
0
 

Author Comment

by:ablove3
ID: 40393566
That's great, Glenn.  I really appreciate your help
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now