How do I select a random number in a list? M...

Aaron_Moolb · 12-17-2018 04:00 PM

How do I select a random number in a list?

My structure is as follows: Two Tables (List Names and List Items)

Right now I have a virtual column in the List Names table that says =SELECT(List Items[Randomizer Number],[List Name ID]=[_THISROW]) This will return a list like this: 1 , 2 , 3 , 4

Then I have another virtual column that says =LEFT(RIGHT([String],LEN([String])-RANDBETWEEN(1,LEN([String]))+1),1) Because the list above has spaces, the LEN expression returns 13. Not good when trying to return either a 1, 2, 3, or 4.

Any ideas?

Aleksi_Alkio

How about… RANDBETWEEN(1,MAX(List Items[Randomize Number]))

Aaron_Moolb

@Aleksi_Alkio I need a filter on that expression…like this:

RANDBETWEEN(1,LOOKUP(MAXROW(List Items,Randomizer Number,[List Name ID]=[_THISROW]),List Items,List Item Key,Randomizer Number))

But I think this is only returning a 1

Aleksi_Alkio

Would you please descibe the case with your own words without any formula so it would be easier to understand what your goal would be, thanks.

Aaron_Moolb

@Aleksi_Alkio My List Items Table is a child of the List Names Table. For the column called Randomizer Number, I restart my count for every list item added.

Example:

List Name Table

_RowNumber = 1 List Name Key = UNIQUEID() List Name = Test

List Items Table

_RowNumber = 1 List Item Key = UNIQUEID() List Name ID = the [List Name Key] List Item Name = test list item 1 Randomizer Number = 1

_RowNumber = 2 List Item Key = UNIQUEID() List Name ID = the [List Name Key] List Item Name = test list item 2 Randomizer Number = 2

Now, when I add a new list and new list items, I restart my [Randomizer Number] counter

Example:

List Name Table

_RowNumber = 2 List Name Key = UNIQUEID() List Name = Test 2

List Items Table

_RowNumber = 3 List Item Key = UNIQUEID() List Name ID = the [List Name Key] List Item Name = test list item 1 Randomizer Number = 1

_RowNumber = 4 List Item Key = UNIQUEID() List Name ID = the [List Name Key] List Item Name = test list item 2 Randomizer Number = 2

With that said, I cant use a =RANDBETWEEN(1,MAX(List Items[Randomizer Number])) because there are duplicate [Randomizer Number] entries based on the List Name ID.

Does that make sense?

Steven_Coile

@Aaron_Moolb, please explain the purpose of all this you’re doing. Don’t tell us how you’re doing it, tell us why you’re doing it.

Aaron_Moolb

+Steve Coile I just need a randomly chosen value from the Related List Items table

Steven_Coile

A clunky way:

INDEX(

FILTER(

“List Items”,

([List Name ID] = [_THISROW])

),

RANDBETWEEN(

1,

COUNT(

FILTER(

“List Items”,

([List Name ID] = [_THISROW])

)

) )

Steven_Coile

Alternatively, and more elegantly, create a virtual column in the List Names table to contain the list of its items:

=FILTER(

“List Items”,

([List Name ID] = [_THISROW]) )

If the virtual column is called My Items, you can the get a random item from it with:

INDEX(

[My Items],

RANDBETWEEN(

1,

COUNT([My Items])

) )

Jon_S

Hey @Steve

I was wondering if you can get more than 1 random value… maybe restrict it with TOP() or something like that.

Here’s what i’ve got but the results are always the same.

TOP(TOP(
SPLIT(
SELECT(
BOOKSTORES[Record_ID],
TRUE
),
“,”
),RANDBETWEEN(1, COUNT(BOOKSTORES[Record_ID]))),5)

My list of BOOKSTORES has about 66 unique ids. I just need to pull 5 random ones.

Note: it’s obiously a VC that i’m working with in another table. All i need is a list of comma-separated IDs. No fancy views or anything like that…

Steve

Try:

TOP(
  ORDERBY(
    BOOKSTORES[Record_ID],
    RANDBETWEEN(1, 999999)
  ),
  5
)

Jon_S

Hats of to @Steve one more time today

Thanks for your patience and help!

Aaron_Moolb

Thank you everyone! INDEX and FILTER works

aloha21

Selecting a random number from a list can be a bit tricky, but don't worry. I've got an idea that might help you out.Instead of using the LEN function, you can try using the COUNT function to get the number of items in your list. Then, use the RANDBETWEEN function with the COUNT result as the maximum value. This way, you'll ensure you get a random number within the range of your list.
By the way, I recently stumbled upon one website at https://flipsimu.com/. It's got virtual coin flippers and dice that you can use to add some randomness to your life. It's pretty cool!