Dan Wilson
Well-known Member
- Joined
- Feb 5, 2006
- Messages
- 536
- Office Version
- 365
- Platform
- Windows
Good day. I am running Excel out of Office365 (updated) and Windows 10 (updated). Please excuse the length of this thread. Over the last 15 years of using this Forum, I have discovered that my explanation of a request for help has been lacking in total definition of the problem.
Over the last few years, I have created a Workbook that helps me to control and manipulate the data from my music folder. I produce a weekly Podcast featuring the music from that folder. There are now 3007 songs in the music folder. The workbook is titled “50-69music.xlsm” and has five worksheets. The major worksheet is titled “50-69” and contains 15 Columns of data about the 3007 songs. The other four worksheets are used to count, sort, track and total the data about the songs.
The worksheet titled “50-69” searches the Music folder on my computer and copies elements from the File Properties of the Music folder such as Title, Artist, Year of release, etc. This creates a list of the Music folder in Rows 2 through 3008. Thus, each individual song has its own Row number.
One of the 5 worksheets titled “Alpha” contains formulas that count the number of songs that start with a chosen character. That character can be “A” through “Z”, “1” through “9”, or an open parenthesis. This worksheet also contains formulas to create random selections in specified Row number groups. With the help of this Forum, I can now create random numbers based on an array of numbers from Rows 2 thru 3008.
What I need help with is identifying the Row numbers of songs whose Title (Column A) starts with a chosen character. Since there are 3007 songs in the worksheet, I can manually examine the Row numbers of the songs that start with the character “A”. In the current worksheet that ends up being Rows 33 thru 189. That totals a count of 157 songs. I can then put the number 157 in the Take formula to create 40 random numbers of songs to pick for the podcast.
As the acquisition of songs increases almost weekly, the count of songs starting with a particular character changes and that means the Row numbers will change and will require manually counting the chosen songs to make the Take function work properly.
Is there a function that will identify the first and last Row numbers of songs with a chosen starting character? That will then make the choice of random selection accurate. Thank you for reading this far and for any help with this issue.
Over the last few years, I have created a Workbook that helps me to control and manipulate the data from my music folder. I produce a weekly Podcast featuring the music from that folder. There are now 3007 songs in the music folder. The workbook is titled “50-69music.xlsm” and has five worksheets. The major worksheet is titled “50-69” and contains 15 Columns of data about the 3007 songs. The other four worksheets are used to count, sort, track and total the data about the songs.
The worksheet titled “50-69” searches the Music folder on my computer and copies elements from the File Properties of the Music folder such as Title, Artist, Year of release, etc. This creates a list of the Music folder in Rows 2 through 3008. Thus, each individual song has its own Row number.
One of the 5 worksheets titled “Alpha” contains formulas that count the number of songs that start with a chosen character. That character can be “A” through “Z”, “1” through “9”, or an open parenthesis. This worksheet also contains formulas to create random selections in specified Row number groups. With the help of this Forum, I can now create random numbers based on an array of numbers from Rows 2 thru 3008.
What I need help with is identifying the Row numbers of songs whose Title (Column A) starts with a chosen character. Since there are 3007 songs in the worksheet, I can manually examine the Row numbers of the songs that start with the character “A”. In the current worksheet that ends up being Rows 33 thru 189. That totals a count of 157 songs. I can then put the number 157 in the Take formula to create 40 random numbers of songs to pick for the podcast.
As the acquisition of songs increases almost weekly, the count of songs starting with a particular character changes and that means the Row numbers will change and will require manually counting the chosen songs to make the Take function work properly.
Is there a function that will identify the first and last Row numbers of songs with a chosen starting character? That will then make the choice of random selection accurate. Thank you for reading this far and for any help with this issue.
