[Solved] Select random data from a list

Discuss the spreadsheet application
Post Reply
BetaRayBill
Posts: 10
Joined: Mon Jul 02, 2018 5:34 am

[Solved] Select random data from a list

Post 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.
Last edited by MrProgrammer on Fri May 21, 2021 4:32 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 4.1.2
MAC OSX Sierra
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Display Data From a List?

Post by Zizi64 »

Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How To Display Data From a List?

Post by Villeroy »

index_rank.ods
pick 10 random items out of 30
(15.4 KiB) Downloaded 222 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How To Display Data From a List?

Post 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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
BetaRayBill
Posts: 10
Joined: Mon Jul 02, 2018 5:34 am

Re: Select random data from a list

Post 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.
Attachments
example.png
Open Office 4.1.2
MAC OSX Sierra
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Select random data from a list

Post 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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Select random data from a list

Post by karolus »

Hallo

@BetaRayBill: Use: =INDEX($A$2:$A$16;RANK(B2;$B$2:$B$16))
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
BetaRayBill
Posts: 10
Joined: Mon Jul 02, 2018 5:34 am

[Solved] Select random data from a list

Post 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.
Open Office 4.1.2
MAC OSX Sierra
Post Reply