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

expressions
(Aaron Moolb) #1

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) #2

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

(Aaron Moolb) #3

@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) #4

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) #5

@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) #6

@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) #7

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

(Steven Coile) #8

A clunky way:

INDEX(

FILTER(

“List Items”,

([List Name ID] = [_THISROW])

),

RANDBETWEEN(

1,

COUNT(

FILTER(

“List Items”,

([List Name ID] = [_THISROW])

)

)

) )

(Steven Coile) #9

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])

) )

(Aaron Moolb) #10

Thank you everyone! INDEX and FILTER works