Candidate and Company Matching System

elicos
elicos ✭✭✭✭✭✭

Hi Everyone,

I'm trying to build a way to match candidates to companies.

And then send those match notifications to both candidate and company.

So I have 2 sheets

Sheet 1 = Candidate List


Sheet 2 = List of Companies Interested


Sheet 3 = Merged Somehow Based on Department Role/Job Category and Location

Would have a unique row for each match.

For example

Candidate 1 matches to Job 1 and Job 2

Candidate 2 matches to Job 2 only

I want to have 3 rows created for each instance of a match.

Row 1 = Candidate 1 Job 1

Row 2 = Candidate 1 Job 2

Row 3 = Candidate 2 Job 2


Please let me know if you have any ideas how this can be done.

I do have access to all smartsheet features and premium apps.


Thanks,

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @elicos

    I don't believe there is a way you can do this by having one option per row.

    One of the complications is the multi-select fields. There isn't a function that can use one multi-select cell and individually match each element in another sheet's multi-select column - it would look for an exact match, meaning the combinations selected ("Remote, Canada" instead of "Remote" and then "Canada"). However if we broke out one sheet's multi-select field into single selections, then we can search for this single selection among another multi-select column.

    What I would do in this instance is set up a sheet that has each Job listed broken out by its Role and Country combinations.

    Ex:

    Row 1 - Company 1 / Operations / Canada

    Row 2 - Company 2 / Operations / Canada

    Row 3 - Company 2 / Operations / Remote

    Row 4 - Company 2 / Sales / Canada

    Row 5 - Company 2 / Sales / Remote


    You'll need to set up each row with separate options, not multi-select. This is so that you can use a JOIN(COLLECT formula to bring in any/every Candidate that matches each option. We wouldn't be able to parse them down multiple rows, but you could collect all the Candidates that match each option:

    =JOIN(COLLECT({Email Column}, {Location Column}, HAS(@cell, [email protected]), {Job Category}, HAS(@cell, [Department Roles]@row)), ", ")

    Ex:

    Row 1 - Company 1 / Operations / Canada / Test 1, Test 2, Test 4

    Row 2 - Company 2 / Operations / Canada / Test 1, Test 2, Test 4

    Row 3 - Company 2 / Operations / Remote / Test 1, Test 4

    Row 4 - Company 2 / Sales / Canada / Test 3

    Row 5 - Company 2 / Sales / Remote / Test 3


    You could then have a Report that Groups by Company so you can see all the possible candidates, however there will be duplicates (like in Row 4 and 5) above.

    I hope this helps,

    Genevieve