Forum Discussion
HELP WITH FORMULAS TO AUTO-POPULATE A TABLE
OK, I've attached something that i think works, but there are a couple of notes.
First and probably most important is that i've added a new column to your candidate table (F) that counts how many matches the candidate has. It's just so much easier to do it this way then trying to create some monster formula elsewhere that does the same thing.
However, in order to be able differentiate candidates in the combined table I've added a little something to the end of the count. The formula looks like this:
=COUNTIFS(JobOrdNum[EmpPref],[@EmpPref],JobOrdNum[2DigZip],[@2DigZip])+(0.0000000000001*ROW())
Its that +(0.0000000000001*ROW()) which means that rather than 3 matches, candidate 5001 has 3.0000000000002 matches. This means however that any candidates that have the same number of actual job matches will be ranked higher the further down the table they are. Giving a possible advantage to people who were put on the list last. I'm not sure if that matters, I'm not really sure how to fix it if it..
apart from that, adding a number to column N will give you the candidate ranked that number, and column Q is an array formula so if it stops working remember it needs to be entered with ctrl+shift+enter
What are you matching candidates and jobs on? EmpPref, status and 2Digzip?
So candidate 5001 would match 3 jobs? 302, 303 and 311?
- Yes, I am matching candidates and jobs by EmpPref and 2DigZip. I should have mentioned ignoring the "Status" column for now. You are correct, Candidate 5001 would match Job Order #s 302, 303, and 311.
OK, I've attached something that i think works, but there are a couple of notes.
First and probably most important is that i've added a new column to your candidate table (F) that counts how many matches the candidate has. It's just so much easier to do it this way then trying to create some monster formula elsewhere that does the same thing.
However, in order to be able differentiate candidates in the combined table I've added a little something to the end of the count. The formula looks like this:
=COUNTIFS(JobOrdNum[EmpPref],[@EmpPref],JobOrdNum[2DigZip],[@2DigZip])+(0.0000000000001*ROW())
Its that +(0.0000000000001*ROW()) which means that rather than 3 matches, candidate 5001 has 3.0000000000002 matches. This means however that any candidates that have the same number of actual job matches will be ranked higher the further down the table they are. Giving a possible advantage to people who were put on the list last. I'm not sure if that matters, I'm not really sure how to fix it if it..
apart from that, adding a number to column N will give you the candidate ranked that number, and column Q is an array formula so if it stops working remember it needs to be entered with ctrl+shift+enter
Philip, thank you SO MUCH for your help! I am excited to use this information to finish my project!
Ronna :)