Select a random name from an uneven table of names


New Member
Nov 11, 2005
:eek: Hello, I am relatively new to writing any sorts of formulas in Excel, so I am severely lacking in neccessary skill.

I am operating on a 98SE system with Excel 2002, and I cannot find my Office CDs to install the analys32.xll add-in.

Currently I have multiple columns of names, alphabetically in order, all "A" names in the 'A' column, "B" in the "B" column, and so on. Some columns have names in the rows 1-250, while others are as limited as 1-20.

I would like to randomly select (and display in another cell, on another sheet) one name out of all the names from A-Z without changing their order.

My recent attempts at solving this problem led me to many forums and message boards, as well as different add-in's that I thought might do the job. I would like to assign a formula, or array, or whatever is neccessary, to a command button in the spreadsheet that will complete this function. Otherwise everytime the screen refreshes it will redo the formula...

So far all I've been able to find relating to this deals with randomly selecting numbers :x , such as '=RAND()' and '=RANDBETWEEN(10,1)', the latter of which requires the analys32.xll add-in which I am so desperately trying to find my CD's and install.

I thought I might have to set up a longer and more complex array calculating a random number from 1-26 so as to pick which letter of the alphabet, (and thereby column) and then based on that, perhaps with an 'if, then, else' formula I could select an appropriate number from either 1-250, or 1-20, or whatever other range of rows is filled with names in that particular letter (column). :rolleyes:

I read something somewhere, I think in the help file, about the 'R1R10' alternate cell reference system dealing with numbers rather than letters, and I thought maybe somehow I could use that and then just combine the first random number (column) with the second (row). But at this point my brain is swirling... :unsure:

Please let me know if there is a simpler formula I could use to accomplish this, and if you've read this far, I sincerely appreciate you taking the time to read my longwinded essay of a post... (my way of apologizing for the length...) :oops:

Thank you for your attention and any responses


Jeremy :)

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi Jeremy, Welcome to the Board!

Do you mean something like:
2Name 1Name 15
3Name 2
4Name 3
5Name 4
6Name 5
7Name 6
8Name 7
9Name 8
10Name 9
11Name 10
12Name 11
13Name 12
14Name 13
15Name 14
16Name 15
17Name 16
18Name 17
Upvote 0

Thank you so much Alan! It worked, might you have any suggestions for selecting a random name from more than one column, ending at different rows, say for instance A1:A20,B1:B18,C1:C5?
Upvote 0
Oh yes, and I forgot to mention, but do you by chance know the coding that could be set up on a command button so recalculated the random name only when you clicked the button?
Upvote 0
Hi Boreal,

Hoew about something like this:

Note I've used D1 for an intermediate value - formula =INT(RAND()*(COUNTA(A:A)+COUNTA(B:B)+COUNTA(C:C)-1)+1)
formula in E1 is =IF(D1<=COUNTA(A:A),OFFSET(A1,D1-1,0),IF(D1<=COUNTA(A:A)+COUNTA(B:B),OFFSET(B1,D1-COUNTA(A:A)-1,0),OFFSET(C1,D1-COUNTA(A:A)-COUNTA(B:B)-1,0)))

No need for a command button - just press [F9]
Upvote 0
Great! That helps a lot, I have to write the code for 26 different columns, each with a different number of rows... :)

The reason I wanted a command button is because I just want the name to be generated at particular times, not every time new information is input into the spreadsheet. A lot more information will be generated randomly, and some will be input by the user. I only want it to generate a random name one time at the beginning.

Upvote 0

so maybe the command button would have code to replace the formula in D1:
Private Sub CommandButton1_Click()
Dim lCount As Long
lCount = WorksheetFunction.CountA(Columns("A:A"), Columns("B:B"), Columns("C:C"))
[D1] = Int((lCount * Rnd) + 1)    ' Generate random value between 1 and lCount.
End Sub

Thus ensuring that the value in E1 changes ONLY when the button is clicked.
Upvote 0
Alan's formula in E1 (will probably be AB1 when columns for all letters of the alphabet are allowed for) will become very long with a COUNTA for each column.

I make the following suggestion. I have used row 1000 in a number of these formulas (noting that the OP mentioned about 250 rows of data) but this could be changed. To make checking the results easier, at the end of each name I have added a number in () to indicate which name each is as if counted from the beginning.

1. Insert a row above all the data.
2. Put a '1' in A1
3. B1 (copied across to C1 - but eventually Z1) is =A1+COUNTA(A2:A1000)
4. The worksheet function to generate the random number is =INT(RAND()*(C1+COUNTA(C2:C1000)))+1 and I agree that this is what the command button should generate and place in cell D1 (eventually AA1). In my sheet I have just entered 16 in D1 as an example. Formula will eventually become =INT(RAND()*(Z1+COUNTA(Z2:Z1000)))+1
5. Formula in E1 is =HLOOKUP(D1,A1:C1000,D1-HLOOKUP(D1,A1:C1,1)+2). Eventually this will be =HLOOKUP(AA1,A1:Z1000,AA1-HLOOKUP(AA1,A1:Z1,1)+2) and be in cell AB1.

This eliminates the need for the very long formula, though it does require the new row 1 being added in. Anyway, use it or leave it as you see fit.
Upvote 0
Thank you Alan for the help both with formula and the command button, and Peter for the providing of more options, I appreciate all the help! :biggrin:

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back