Candidate and Company Matching System

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,

Genevieve P.

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, Country@row), {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