Random Pair Generation with Multiple Criteria (index/match)

Hello everyone,

I am having an excel formula problem, and it would be greatly appreciated if you could help me out.
Long story short, here is my attached document.


I am trying to create a list of random pairs with this criteria:
Criteria
1) Two people to be paired up randomly, but
2) Two people to be from the SAME cluster (Same Cluster Code)
3) Two people to be from the DIFFERENT department (Different Department Code)
4) Should be automatic calculation -no manual rand() sorting



but the problem is, it keeps showing me duplicate. For example,
If Frederica and Rosie are paired up, then Cristen should not be matched with Rosie, etc.

Please help!

Hello Phuocam ,

I used your formula, and it seemed to be working for the one time use.
I want the random match everytime i run the formula, but it seems like one person is stuck with its match forever.
How can we fix that?

Here is a possibility.
The TableMMSummary9 table is appended with a column (which may be hidden using the grouping feature for aesthetic purposes) of randomly generated numbers.
A second table (2), which could be moved and/or hidden for aesthetic purposes, is populated using the following formula for the first three columns:

Formula:

=INDEX(TableMMSummary9[Full Name],MATCH(SMALL(TableMMSummary9[[Rand()]:[Rand()]],ROW(1:1)),TableMMSummary9[[Rand()]:[Rand()]],0))

Phuocam's formula populates the fourth column.
The Matched Person's Name column in the TableMMSummary9 table is populated using:

Formula:

=INDEX(Table2[Matched Person''s Name],MATCH([@[Full Name]],Table2[Full Name],0))

Test by pressing the F9 key.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. A good way to see what a formula is doing is to select a cell containing the formula and run the Evaluate Formula feature (Formulas tab in the 2010 version). Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

Administrative Note:

Hello akshat418 and Welcome to Excel Forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Let us know if you have any questions.