Matching two columns in Apps Script / JavaScript with a specific condition-CodePudding

I'm working on matching a pair from a list of names in Apps Script but there's a condition attached to it which I'm trying to figure out.

<table>
<thead>
<tr>
<th>Names1</th>
<th>Names2</th>
</tr>
</thead>
<tbody>
<tr>
<td>Glen</td>
<td>Adam</td>
</tr>
<tr>
<td>John</td>
<td>Matthew</td>
</tr>
<tr>
<td>Mitch</td>
<td>Damien</td>
</tr>
<tr>
<td>Daryl</td>
<td>Jason</td>
</tr>
<tr>
<td>Steve</td>
<td>Shane</td>
</tr>
<tr>
<td>Ricky</td>
<td>Stuart</td>
</tr>
</tbody>
</table>

In the above example, Glen from Names1 column is mapped to Adam in Names2 column. I wish to write a randomization matching code where Glen gets mapped with every other person from Names1 and Names2 column without any repeating matches. This is what I have tried so far -

Method 1: Use a simple randomize() function but repeating matching occurs at a higher frequency

Method 2: Using a custom shuffle function

function shuffleArray(range) {
     var i,j,temp;
     for (i=range.length-1; i>0; i--) {
         j = Math.floor(Math.random()*(i 1));
         temp = range[i];
         array[i] = array[j];
         array[j] = temp;
     }
     return range;
}

Unfortunately, this method also shows repeated matching but at a very less rate and it does not do an exhaustive matching with every other person in both the lists.

Any help would be really appreciated. Thanks!

CodePudding user response：

You could store what you already get in an array and everytime you get a pair check first if the array does not already contain the pair you found. If you want every possible pair, without duplicates you can do something like this:

const found = [];
for(let i=0; i<range.length; i  ){
  for(let j=i 1; j<range.length; j  ){
    const str = `${range[i]}_${range[j]}`;
    if(!found.includes(str)){
      found.push(str);
    }  
  }
}

CodePudding user response：

In the above example, Glen from Names1 column is mapped to Adam in Names2 column. I wish to write a randomization matching code where Glen gets mapped with every other person from Names1 and Names2 column without any repeating matches.

Logically, why would you randomize this and not just match all unique names, then randomize the order of the results?

Solution:

function myFunction(personName) {

  const personList = my2ColumnArray.flat()

  const pairings = [...new Set(personList)].filter(i => i !== personName)
                                           .map(i => i = [personName, i])

  for (let i = pairings.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i   1));
    [pairings[i], pairings[j]] = [pairings[j], pairings[i]];
  }

  return pairings

}

Commented:

function myFunction(personName) {

  // Get a single list of all names.
  const personList = my2ColumnArray.flat()

  // Get a list of unique names, removing the `personName`.
  const pairings = [...new Set(personList)].filter(i => i !== personName)
  // With all remaining names, pair `personName` with each name.
                                           .map(i => i = [personName, i])

  // Shuffle the array of pairings.
  for (let i = pairings.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i   1));
    [pairings[i], pairings[j]] = [pairings[j], pairings[i]];
  }

  return pairings

}

See Also:

CodePudding user response：

It is unclear why you want to do this in Apps Script, because a plain vanilla spreadsheet formula should suffice. Assuming Names1 is in A2:A and Names2 in B2:B, put this formula in cell D2 or another free range:

=transpose( { A2; filter( B$2:B, len(B$2:B), A$2:A <> A2 ) } )

Then copy the formula down to extend it to the other names.