Microsoft Office Questions? Ask a IT Expert for Support ASAP
Titles refer to date location of biological field surveys. Need random selection for 1 of 45 surveys, repeated 100 times. To develop accounting and projection of number of surveys to replicate redundancy in species richness observed. i.e. some surveys may see 5, 7, or 10 species. I need a tool to randomly select the species list to develop this projected curve.
Only tool I need is a macro to randomly pick a cell (title) from a given number of cells (i.e. A1 - A45), and repeat the process 100 times while displaying all picks in the order they were generated.
Can display after completing the 100 calculations, with showing all 100 answers in sequence they were chosen.
Thank you for patiently waiting. After few tries, I came up with a sample Excel file containing a macro to do the tasks you desired.The logic here is that you need to enter the master list with a sequence numbers 1 - 45. Then opposite it, enter the corresponding titles. This will serve as a master list since the new random list will be derived in this master list.What we can randomize are numbers only. So we use the RANDBETWEEN function to randomize the numbers between 1 to 45. The results of course will be numbers within the range. We need to perform the randbetween function for the 100 cell that you want to list the random 100 titles. Now, we will pair the randomized number (100 of them randomly between 1 to 45) with the corresponding title in the master list using the VLOOKUP function. Here is my sample file, download link below:http://wikisend.com/download/459126/random_titles_generator.xlsmPlease note that the file is a Macro-Enabled file, XLSM. That means that the file contains a macro. However, the only code in the macro is the function "list_title" which will only "Calculate" the range A2:B101 in the Random_List sheet when the button is clicked. The randbetween function will trigger after every "calculate" command.NOTES: The master list can be in the same worksheet, the file is a sample file so you can just change the references of the cells.Please remember to rate my service positively (3-5 stars/faces) once you have all the information you need. Tips are always highly appreciated! If you have any other questions, please ask me or reply to me – I’ll be happy to respond. Thank you!Best regards,Jess
Works great. After I sent my last message checking on the status, the board refreshed showing that you finished the calculator.
Really appreciate it,