Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

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
0
ablove3
Asked:
ablove3
  • 9
  • 4
  • 3
2 Solutions
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now