Page 1 of 1

[Solved] Select random data from a list

PostPosted: Tue May 11, 2021 6:02 am
by BetaRayBill
Hi,
This is the problem: I created a spreadsheet with a simple list of items. I opened another spreadsheet (same document) and wanted to create a cell(s) with a function that randomly selects an item from the previously created list and displays it (renewing each time I reopened the document). The function would take place in many cells, so it would be great if they didn't repeat. I did some research, but it's not as easy to do as I thought - something about assigning each item in the list a random number using Rand(), and then having that selection displayed; but the parameters are confusing and the function eventually doesn't work (Error508, etc.)

Thanks.

Re: How To Display Data From a List?

PostPosted: Tue May 11, 2021 12:13 pm
by Zizi64
Please upload your ODF type sample file here.

Re: How To Display Data From a List?

PostPosted: Tue May 11, 2021 3:52 pm
by Villeroy
index_rank.ods
pick 10 random items out of 30
(15.4 KiB) Downloaded 39 times

Re: How To Display Data From a List?

PostPosted: Tue May 11, 2021 4:09 pm
by MrProgrammer
BetaRayBill wrote:I opened another spreadsheet (same document) and wanted to create a cell(s) with a function that randomly selects an item from the previously created list and displays it (renewing each time I reopened the document). The function would take place in many cells, so it would be great if they didn't repeat.
Tutorial with examples: Randomization in Calc

BetaRayBill wrote:using Rand() … but the parameters are confusing and the function eventually doesn't work (Error508, etc.)
Read the documentation for the RAND function. It doesn't take any parameters! How can that be confusing?

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

Re: Select random data from a list

PostPosted: Thu May 13, 2021 9:05 pm
by BetaRayBill
Here is a screenshot of an example I found that I thought matched my problem. It's a list of names, followed by a random set of numbers (using Rand() function). The next column is where the random names are to appear. The function shown looks like it should work (supposedly won't repeat a name), but the output is an error.

Re: Select random data from a list

PostPosted: Thu May 13, 2021 9:17 pm
by MrProgrammer
BetaRayBill wrote:Here is a screenshot …
Your screen image is not the proper way to get assistance with analyzing the error in your sample problem. I will presume the goal is to present the 15 names in random order. If not, state the goal clearly. Can't you use the example for section E (sheet RandSort) of the tutorial I provided? You will need to attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself) before I can help with your sample problem. A guess though: In OpenOffice you must use semicolon as an operand separator in formulas because using a comma can create Err:508.

Your signature shows you use OpenOffice 4.1.2. You should know that this version has a serious problem which prevents many of the features of Base from working. Even if you don't use Base now, you might want to in the future, for example, to create mailing labels.

Re: Select random data from a list

PostPosted: Fri May 14, 2021 10:22 am
by karolus
Hallo

@BetaRayBill: Use: =INDEX($A$2:$A$16;RANK(B2;$B$2:$B$16))

[Solved] Select random data from a list

PostPosted: Mon May 31, 2021 8:26 pm
by BetaRayBill
Hey Everyone,
Sorry for these late replies. This particular problem is not an emergency, and I haven't had the time to check back on this forum as much as I would like. Mr. Programmer: my apologies for not being clear. I will look into the semicolon suggestion. Karolus: I plugged in your formula, and that netted the result I wanted - all the names were randomized and didn't repeat. Thank you for that! I will examine the formula to try to figure out exactly how it works so as to understand it better. The actual list I will be extracting from is much larger than the example, so we will see how it works with that. It looks like it should be fine though.

Thanks to all.