Draw a Random name from a list

[brianfosterblack]

Can anyone please help me with this. I have a command Button on the sheet which I want to run the code to do the following in cells G20 to G100 I have a list of names. The list can be any length - so there might only be 12 names or 20 names but the list always starts at cell G20 and will never exceed G100 I want to randomly draw 1 name from this list and post the result in cell S4 If the name found has the left 5 letters as "(Vis)" then it must draw another name. Every time I press the command button it will select another name from the list and it can select the same name again, just not a name with the left 5 letters of "(Vis)" I have tried to customize other code without success so any assistance will be appreciated

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub GetRandomName()
  Dim LastRow As Long, Arr As Variant
  LastRow = Range("G20").End(xlDown).Row
  Arr = Split(Replace(Replace(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF(LEFT(G20:G#,5)<>""(Vis)"",SUBSTITUTE(G20:G#,"" "",""|""),"""")", "#", LastRow))))), " ", "@"), "|", " "), "@")
  Range("S4").Value = Arr(Application.RandBetween(0, UBound(Arr)))
End Sub[/td]
[/tr]
[/table]

 

[brianfosterblack]

Hi Rick,

thanks for your reply. It is working that it is drawing a name from the list but it is repeatedly only drawing one of the last 2 names. It is not randomly drawing from the whole list

 

[steve case]

This should work:

Copy this formula down column G

=IF(H1="","",RAND())​

Put your list down Column H
Put this formula in Cell S1

=H1​

Use the sort function and sort
G1:H100 for Column G Ascending

Your random selection will appear in Cell S1

Write a little macro for the sort and attach to a button etc.

 

[brianfosterblack]

Thanks Steve but I cannot do this as I have information in the other columns which relate to the name, so the name needs to remain where it is. That is why the name list is in column G and not in column A. The route Rick has proposed is how we need to work but his macro is not selecting the names randomly - just the last 2 names.

 

[steve case]

Hmmmm I need to read all the instructions Duh!

 

[Rick Rothstein]

Hi Rick,

thanks for your reply. It is working that it is drawing a name from the list but it is repeatedly only drawing one of the last 2 names. It is not randomly drawing from the whole list

The code works correctly for me. Off the top of my head, there are only two situations where my code could be doing what you are reporting... one, your data starts in a lower number row than Row 20 where you said it started and ends at cell G22 or... there are blank cells within your list that you did not tell us about and in your current workbook, the first blank is in cell G23. Other than that, I cannot think of how my code could be doing what you say it is doing. If your names in Column G do not fall into one of those two scenarios, you will need to post a copy of your workbook online (using dropbox or some other such file sharing service) so that we can watch what you report happening directly while running my code.

 

[brianfosterblack]

Hi Rick,

Sorry, I am an idiot. My list starts at G14 and when I make that change in your code it works perfectly. Thank you and my apologies.

 

[steve case]

Mods - Please delete this message

​