Formula to pair staff based on criteria

ABoado
ABoado ✭✭✭
edited 02/24/23 in Formulas and Functions

Hello Smartsheet community,

An idea came up recently about pairing staff up who work in different divisions for a random coffee chat to increase staff engagement. We could use Smartsheet to create a form to gather info on staff who are interested in participating and what division they belong in but is there a formula I can use in the sheet that can match staff up based on the criteria that they are not in the same division? And then when staff are matched, is there a way to set up an automated workflow in which the matched staff are notified that they have been paired? Any ideas are welcome! I was thinking Index Match and a helper sheet might be helpful here but wasn't sure. Thanks in advance!!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @ABoado

    What a fun idea! 🙂

    You could use a JOIN(COLLECT function to show all possible matches per person based on your criteria (not the same department):

    =JOIN(COLLECT(Name:Name, Department:Department, <>Department@row), CHAR(10))


    Using this column as a guide, select a name from the list and type it into a manual-entry Text/Number column:


    Then, I have a "Final Match" column set up then based on this Test Match that I entered to show the matching pair for each person. This is set as a column formula.

    =IF([Test Match]@row <> "", [Test Match]@row, IF(COUNTIF([Test Match]:[Test Match], Name@row) > 0, INDEX(Name:Name, MATCH(Name@row, [Test Match]:[Test Match], 0))))


    I set up a conditional formatting rule to turn the blank Test Match cells grey if that person has a match based on a previous selection. That way I don't accidentally put in a second match for someone (such as Joe on the second row).


    Finally, I adjusted the "Possible Matches" column to eliminate options once they've been selected as a final match, so you only see new possible matches to write in your Test Match column:

    =IF([Final Match]@row <> "", "MATCH FOUND", JOIN(COLLECT(Name:Name, Department:Department, <>Department@row, [Final Match]:[Final Match], ""), CHAR(10)))


    You can of course rearrange columns or change the formatting if it's easier to see the matches together:


    I hope this helps!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @ABoado

    What a fun idea! 🙂

    You could use a JOIN(COLLECT function to show all possible matches per person based on your criteria (not the same department):

    =JOIN(COLLECT(Name:Name, Department:Department, <>Department@row), CHAR(10))


    Using this column as a guide, select a name from the list and type it into a manual-entry Text/Number column:


    Then, I have a "Final Match" column set up then based on this Test Match that I entered to show the matching pair for each person. This is set as a column formula.

    =IF([Test Match]@row <> "", [Test Match]@row, IF(COUNTIF([Test Match]:[Test Match], Name@row) > 0, INDEX(Name:Name, MATCH(Name@row, [Test Match]:[Test Match], 0))))


    I set up a conditional formatting rule to turn the blank Test Match cells grey if that person has a match based on a previous selection. That way I don't accidentally put in a second match for someone (such as Joe on the second row).


    Finally, I adjusted the "Possible Matches" column to eliminate options once they've been selected as a final match, so you only see new possible matches to write in your Test Match column:

    =IF([Final Match]@row <> "", "MATCH FOUND", JOIN(COLLECT(Name:Name, Department:Department, <>Department@row, [Final Match]:[Final Match], ""), CHAR(10)))


    You can of course rearrange columns or change the formatting if it's easier to see the matches together:


    I hope this helps!

    Cheers,

    Genevieve

  • ABoado
    ABoado ✭✭✭

    Thank you so much Genevieve! I will try this out! super awesome!! I'm excited to increase staff engagement with this new idea.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!