Help with the Random Function

[Dan Wilson]

Good day. The following is going to take a long explanation. I produce a weekly podcast featuring music from 1950 through 1969. The music is in a Folder on my computer and contains over 3000 songs. Over the last few years I have created and edited a Workbook that tracks the songs in that Folder. The Workbook now contains five Worksheets that access and store seven File Properties from the Music Folder.

The File Properties are -
Title – the Title of the song
Artist – the Artist(s) who recorded the song
Mult – how many versions of the songs exist
Year – the year the song was recorded
# - if the song Charted, the highest rating
Length – the length of the song in minutes and seconds
Genre – the Genre of the song (Oldies, Rock, Folk, etc.)
Intro – the non-verbal introduction time in minutes and seconds.

The workbook titled “50-69 music.xlsm” contains 5 worksheets.

50-69 – this worksheet houses all of the file properties listed above in Columns A through H. There are additional Columns controlled by Macros for sorting.
Macros- this worksheet contains ten Buttons. Each button has an Assigned Macro to sort the 50-69 worksheet as desired.
Counts - this worksheet displays various totals of file properties. Totals – this worksheet shows totals of desired file properties.
Alpha - for lack of another name. This worksheet contains my experimentation with the Random Function.

This is where the question arises. There are 26 Alphabetic characters, 9 digits, and the “Open parenthesis” character. Row 1 contains each of the mentioned characters in Columns A through AJ. Row 2 contains the Formula =SUM(Countif(’50-69’!A:A,{“A*”})).
That formula is then extrapolated from A2 to AI2 to tell me how many songs start with each of the listed characters.

Row 4 contains numbers from 1 to 40 in Columns A through AJ.
Row 5 contains the formula =RANDBETWEEN(1,40) in Columns A5 through A40. Executing the F9 character on my keyboard causes all of the numbers in Row 5 to change. The problem occurs in the fact that each random function executes without the knowledge of any of the other Columns. That means that multiple occurrences of the same random number can occur.

Another Row was then used to generate random numbers between 1 and 3000. The chances of duplicates at this point are much slimmer but can still occur.

Is there any function to examine all 40 of the randomly generated numbers to identify identical numbers or any other way to fix this?

As always, I appreciate any help with this issue.

 

[Fluff]

If you want to generate all 40 numbers in a random order, you can use

=SORTBY(SEQUENCE(,40),RANDARRAY(,40))

 

[Dan Wilson]

Good day Fluff and thank you for answering so quickly. Apparently, I did not make the problem clear. I looked in Help to find out how to use the Sortby Function and it looks like it applies to a known set of values. When I want to choose random songs from my music folder, it is not always just 40 songs to choose from. For instance, there are 157 songs in the music folder that begin with the letter “A” and when the 50-69 worksheet is sorted by Title, I want to find 40 random numbers between 1 and 157. Perhaps I don’t understand the Sortby Function, but it appears that if I enter 1-157, I will get 157 random numbers instead of 40. I don’t want the order of the song properties changed. I hope that helps.

 

[Fluff]

In that case how about

=TAKE(SORTBY(SEQUENCE(,157),RANDARRAY(,157)),,40)

 

[Dan Wilson]

Good day again Fluff and thank you for staying with this. Please excuse my delay in getting back to you. I spent yesterday from 6 AM until 8 PM working as a volunteer at my local Election Board. I tried using your suggestion and as best I can understand it, it appears to be going in the wrong direction. Let me try my explanation another way. Using the worksheets listed in my original thread, let's take the following steps:

In the worksheet titled "Alpha"
Row 1 will contain the numbers 1 thru 40 for no reason other than showing that there will be 40 results of the RANDOM function placed in Row 2.
At this point the worksheet titled "50-69" contains 3007 songs with Column A containing the Title of each song sorted alphabetically.
To make the Random function work, there are 157 songs in "50-69" starting with the letter "A" in Rows 33 thru 189. There are 172 songs in Rows 190 thru 361. Those numbers will continue to change thru all 3007 songs.
Row 2 will contain the formula =RANDBETWEEN(33,189) copied in Columns A thru AN of Row 2 for the songs starting with "A".
Row 2 will then be changed to =RANDBETWEEN(190,361) copied in Columns A thru AN of Row 2 for the songs starting with "B".
This same procedure would then be used to create random numbers for all the songs in the music folder as desired.
Since the formulas stated above execute the RANDOM Function separately for each Column, I have found that the number of duplicates being created varies with the difference of the numbers being randomized. Is there a Function that I can use to find these duplicates or a way to create the random numbers without affecting the sort of the "50-69" worksheet. Perhaps I don't fully understand the TAKE, or SORTBY functions, but the HELP descriptions lead me to believe that the sort of the worksheet "50-69" will be changed.

Thank you if you stick with this one. I won't be surprised if there is no easy answer to my problem. I can always visually spot the duplicates as I plan the schedule for the Podcast, but any exercise of the RANDOM function affects the entire worksheet. I don't know any way to execute the RANDOM function for just one cell.
Thank you, Dan Wilson...

 

[Fluff]

This will give 40 random numbers between 33 & 189 with no duplicates.

=TAKE(SORTBY(SEQUENCE(,157,33),RANDARRAY(,157)),,40)

 

[Dan Wilson]

Absolutely amazing!!! Somehow, sending you an apology does not seem to be enough. Your last formula works great! Now I know what has to be done to complete this task. Please excuse my ignorance with this issue. I read through the Help file, but did not understand what was happening. I still don't. I know what works, but not how or why. I will continue to work on it. Thank you again for the right solution and for being patient with my ignorance.

 

[Fluff]

You're welcome & thanks for the feedback.