How do I shuffle values in a column

Folks question from a Coda noob: I’m trying to shuffle a column in a Table. Any suggestions on the best way to do this.

I am trying to set up 1:1 meetings between a set of people that are matched at random. I have table where Column A has all the user names and in Column B I would like to take this list, shuffle it and match them up with usernames in Column A.

thanks so much

I think I’d do something like this:

Column B will be type Lookup, to this table (let’s call the table People)

First, we can add a column (we’ll call it RandomNumber) with formula Random() to give everyone a random number (we’ll use this for sorting everyone in a random order).

We can also add a column (we’ll call it MyNumber) with formula [People].Find(thisRow), which will spit out a row number (see more about that here)

Then we need to choose our match for each person.

A basic approach would look like this for the Column B formula:

[People]
  .Sort( true, RandomNumber )
  .Nth( thisRow.myNumber )

which means:

  • take everyone in the people table
  • sort them by the Random Number column we created
  • grab one of the names from the list. which name? if I’m the person in row #4, select the 4th person in the randomized list

This still has some problems though, for example that you can get matched with yourself. You need to finagle a .Filter( currentValue != thisRow ) in there somewhere, but it’s gonna throw off your position numbering. Can someone step in to finish this bit off?

Dear @Caesar_Sengupta,

Maybe this post will be helpful for your project:

Ok here’s quite a deep dive into this topic:

It’s really bugging me that I can’t come up with a quick solution to this.

Ok here you go. I had to make a Pack in order to do it lol but let me know if this works:

And here’s the Pack:

2 Likes

Really impressive @Nick_HE :trophy:

A good sample case of what powers :muscle: the packs can have for certain challenges in the CFL

Your detailed explanation is much appreciated too, this step by step explanation makes your logic much more clear. :receipt:

Here’s my solution. I’m not sure if it’s still relevant but hopefully you’ll find it useful!

The working is principle is as follows:

  1. Collect a random item from the original list.
  2. Append this item to a list of other randomly selected values
  3. Make the list of randomly selected values unique
  4. Repeat until the unique list has the same length of the original list.

To start again, clear out the buffer list.

1 Like

I’ve added a demo doc outlining some other applications - e.g. if you want a true shuffle of a list (vs matchups like this), it includes formulas for that

1 Like

Dear @Nick_HE,

Thanks sharing this precious guide on the “Randomizer” :gem:

You might want to consider to switch of these alerts, just for a better user experience

1 Like