Macro to determine eligibility

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
ablove3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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
ablove3Author Commented:
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
Martin LissOlder than dirtCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ablove3Author Commented:
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
ablove3Author Commented:
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
Martin LissOlder than dirtCommented:
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
ablove3Author Commented:
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
ablove3Author Commented:
I'll remember your tip the next time about correcting a post, thank you.
0
ablove3Author Commented:
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
Martin LissOlder than dirtCommented:
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
Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:
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
ablove3Author Commented:
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
ablove3Author Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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
ablove3Author Commented:
That's great, Glenn.  I really appreciate your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.