Forum Discussion
HELP WITH FORMULAS TO AUTO-POPULATE A TABLE
Hello! I have a 2-part problem I need help with, please.
First, I need help with formulas to auto-populate a table (example included). I have Candidate information in the blue table ("Candidates") and Job Order information in the orange table ("JobOrdNum"). I need help with the green table ("Combo") in writing / finding / understanding the formulas that will auto-populate each of the 3 columns. Basically, I need the green table to show which candidates match with which job orders.
Second, once the green table correctly shows the match between the candidates and job orders, then I need two things. (A) I need the top 5 candidates who match the most job orders listed from largest to smallest from cells N2 to N5. (B) Then I need all of this to be dynamic, so that as candidates are added to the blue table and job orders are added to the orange table, the green table automatically updates, including updating the "Top 5" candidates with the most matches.
Thanks in advance for the help!
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?
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
