Random text from list IF letter begins with...

**steveburnstv** · 04-17-2020, 01:00 PM

Hi all, hope you're well.

I've been using the randbetween function to pick a random name from a list (thousands of names in the list).

I'm looking to take it a step further though.

For example:

Return a random name from the list, but only from names in the list that begin with the letter S.

Would there be a way to use the LEFT or MID functions with RANDBETWEEN to do this?

So it would essentially find the letter S in the list, and return a random name from the list beginning with S.

I hope this makes sense?

Would appreciate any input,

Thanks
Steve

**Bernie Deitrick** · 04-17-2020, 02:16 PM

For this to work, your list must be sorted alphabetically in column A. I have written for up to 10000 entries - just don't use the complete column in place of a defined number of rows or it will be reallllly slow

Array-enter (enter with Ctrl-Shift-Enter)

=INDEX(A:A,SMALL(IF(LEFT(A1:A10000,1)="S",ROW(A1:A10000)),1)+RANDBETWEEN(0,COUNTIF(A:A,"S*")-1))

or enter S into a cell - say, B1 - and change the formula (still array-entered) to

=INDEX(A:A,SMALL(IF(LEFT(A1:A10000,1)=B1,ROW(A1:A10000)),1)+RANDBETWEEN(0,COUNTIF(A:A,B1&"*")-1))

**Kaper** · 04-17-2020, 02:48 PM

another option (no sort required) - use simple hepler column (say your names are in Column A, and start from row2 as in Row1 there is a header)
and if the first letter is in D2 (if not it can be written directly into formula) then in B2 (and copy down):
=IF(LEFT(A2,1)=$D$2,RAND(),"")

and selected name For instance in E2:

Formula:

Please Login or Register  to view this content.

Options:
if you need more random names (without repetitions) starting with S then =INDEX($A$2:$A$16,MATCH(LARGE($B$2:$B$16,ROW(A1)),$B$2:$B$16,0))
if there is a chance you will select all use =IFERROR(INDEX($A$2:$A$16,MATCH(LARGE($B$2:$B$16,ROW(A1)),$B$2:$B$16,0)),"_no more names_")