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.
[Solved] Select random data from a list
-
- Posts: 10
- Joined: Mon Jul 02, 2018 5:34 am
[Solved] Select random data from a list
Last edited by MrProgrammer on Fri May 21, 2021 4:32 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
Open Office 4.1.2
MAC OSX Sierra
MAC OSX Sierra
Re: How To Display Data From a List?
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.
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.
Re: How To Display Data From a List?
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: How To Display Data From a List?
Tutorial with examples: Randomization in CalcBetaRayBill 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.
Read the documentation for the RAND function. It doesn't take any parameters! How can that be confusing?BetaRayBill wrote:using Rand() … but the parameters are confusing and the function eventually doesn't work (Error508, etc.)
[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).
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).
-
- Posts: 10
- Joined: Mon Jul 02, 2018 5:34 am
Re: Select random data from a list
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.
Open Office 4.1.2
MAC OSX Sierra
MAC OSX Sierra
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Select random data from a list
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.BetaRayBill wrote:Here is a screenshot …
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).
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).
Re: Select random data from a list
Hallo
@BetaRayBill: Use: =INDEX($A$2:$A$16;RANK(B2;$B$2:$B$16))
@BetaRayBill: Use: =INDEX($A$2:$A$16;RANK(B2;$B$2:$B$16))
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
-
- Posts: 10
- Joined: Mon Jul 02, 2018 5:34 am
[Solved] Select random data from a list
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.
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
MAC OSX Sierra