In the example shown, the formula in G7 is: Note: this formula uses the named range "data" (B5:E104) for readability and convenience. As the minimum value is always 1 for any list, so well always insert 1 manually. Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. I just want to say thanks for these emails. In plain english: Given question 2 is correct, but question 1 is not, to determine the comment for question 2 just select a random comment from the list; however, if both questions are correct, then the comment for question 2 must be randomly selected from the original list, not including the comment that was given for question 1 (no duplicates). Try the following formula: =CHOOSE (ROUND (RAND ()+1);"MBA";"MSc") Our goal is to help you work faster in Excel. In the example shown, the formula in D5 is: = TEXTJOIN ("",1, INDEX ( chars, RANDARRAY (6,1,1,26,TRUE))) where chars is the named range B5:B30 containing the letters A-Z. Is it correct to say "The glue on the back of the sticker is dying down so I can not stick the sticker to the wall"? You must have JavaScript enabled to use this form. Only the XLOOKUP function can perform the same previous operation, just well have to use it as an array formula. ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. One way is to use the RANDBETWEEN function. How to generate random string from a set of available strings in Excel? As the formula is copied down the column, it generates a new 6 . 5. Step 1: Fill the list of names that you have in any column without worrying about the order. ..but I'm not sure how to make that work in Excel. Duplicate names are filled in the specified cells occasionally. Awesome, I didnt realise you could do in-line filtering like that, thanks heaps! Here I will post excel related useful articles. I'm Dave Bruns, and I run Exceljet with my wife, Lisa. You are making a difference in peoples lives. You need to use the function Mid to extract individual characters.. You can write a function to return a random string. Then the INDEX function returns the corresponding value from the list according to the number. Finally, the INDEX function will extract the value according to the number. Inserting List Range Using COUNTA Function, 2. Lets go forward to explore it. So the CHOOSE function will take that number and returns the text value corresponding to the number. I love to learn new things and work with them. How to do a partial string comparison in Excel? function onCatChange() {