Solved

Macro to determine eligibility

Posted on 2014-10-19
16
84 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 46

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 46

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 46

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 45
AutoFilter on a list of items.  Totalling each item in the list. 14 12
Copy value from a certain cell 5 24
ADD New Entries 7 16
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 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

22 Experts available now in Live!

Get 1:1 Help Now