Free Microsoft Excel 2013 Quick Reference

randomly select numbers

Is there a way that I can randomly select two, three or more numbers
from a row containing 10 numbers

jollycarrier's Profile:
View this thread:

Post your answer or comment

comments powered by Disqus
How to generate a list of randomly selected numbers within a range?

Suppose that I would like to randomly select 5 numbers by using "=rand()" or "randbetween(X,X)", what can I do in order to avoid duplication?

Thanks in advance, NueK

I have a column of numbers and want to choose some of them by random. First,
I generate a coulmn of random numbers by RAND function and in next column
select from them by following command:
Now I want to sort these numbers (last column), let say Ascending. How can I
do this?
I am looking for a function or series of functions to perform sorting in a
different column without using Excel-built-in Sort commnad. The reason is
that, sort command doesn't work with randomly generated numbers, because each
time I try to use SORT command, Excel tries to regenerate them.
One poor solution is to copy/special paste (only values) the random numbers
and then use SORT command, but in this way I miss the regeneration function
of random numbers.
Any help is welcome
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

I need to put the numbers from 1 to 54 in a list where each number is randomly
selected. A lot like choosing bingo balls, but once the ball is chosen, it can't
be chosen again.

How do I put 1 to 54 in random order with no duplicates and none missing?


Could any one kindly help with a simple way of randomly selecting six numbers from any given fifty numbers.
Thanks in advance.

I am trying to set up a "daily tip" msg box for using a complicated spreadsheet I have designed. I have about 65 tips typed up, and each one is assigned a number. (1-65) Is I wast to ask the user if they would like to see the daily tip, and if yes then have excel randomly select one of the numbers (1-65) and show that numbers corrosponding tip.
Any ideas?

How can I randomly select six different numbers at any one time i.e lottery
numbers. I have tried using the formula
=INT(RAND()*49)+1, which works OK but I get repeated numbers, can anyone


I was wondering if anyone can help me with a program code which randomly selects a group of numbers which is half of what I input in the cells.

For example, I would key in 10 numbers ie 1, 7, 9, 13, 26, 27, 34, 45, 48, 52 either in a row or column. Then the idea is to randomly select and display half of the numbers with a click of a button.

Thanks in advance for the help.


I am trying to randomize selected components of a list, leaving other parts of the list the same. The problem I'm having is representing an two arrays that are in the same column, but non-consecutive, as one array in the equation I built for randomizing the list. See attached file. Thanks for any help you can give.

I am trying to randomly select a repeating number in a matrix. I have seen the different random number and random select codes out there, but specifically what I'm asking is how to go about selecting a number that repeats in a matrix multiple times, not numbers between a certain range or a specific number that occurs in a matrix either. In other words, say I have a matrix like the following:
0 0 0 0 0 0 5 0 0 0 5 0 4 4 0 0 4 0 0 0 0 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5 0 5 0 8 0 0 0 9 0 0 0 0 0 0 5 0 5 0 0 There are multiple 5's here, but for my purposes I only want to select one of those 5's. Is there a way to do this in Excel?

Also, I can only run macros in MSExcel 2004 for Mac because 2008 doesn't allow macros. Hopefully that doesn't cause any probs.


I'm a newbie in the Excel world and I'm hoping that someone can help me with a macro. Context: column A contains 66 entries, with numbers 1 to 11 where each number has its corresponding number of cells associated. I.e. 1 has 1-cell, 5 has 5-cells and 11 has 11-cells.

I'm hoping to have some help with a macro that would:
make an initial random selection from the 66 cells,place that number in an ordered ranked column on the same sheet - ranking from 11th place to 1st place,remove all the corresponding number cells equal to the number initially chosen (i.e. if the initial random pick is 10, then all cells with the number 10 would be excluded from the next random selection),Restack the remaining numbers in column A and do the random selection again, continue until there are no numbers left in column A and all 11 numbers are ranked in reverse order in another column on the same sheet.I am really not sure how to start this off but I reviewed some of the posts in this forum and I found this code for the random part. I hope that someone can help me.


Sub PickRandom()
MsgBox Cells(Rnd * (Cells(Rows.Count, 1).End(xlUp).Row - 1) + 2, 1)
End Sub

I have a lot of ID numbers and I want to randomly select 10 of them. I already tried the sampling, but that one repeats some of the numbers at times. Is there a way to randomly select unique numbers in Excel?


Is there a way that I can randomly select one number from a set of two or more If I wanted to select one number from the set 3,6,8,15.

Hi all,

I have an array of 20000 numbers containing both 0s and numbers >0. I'm after a way of randomly selecting just 90 of the non-zero numbers from this array and pasting them to a new column. I want them pasted in the correct row position, and the blanks to be replaced with zeros.

Put another way, I need a way of copy and pasting the array to a new column, and replacing all but 90 of the numbers >0 with 0s. I'll need to do this a few times on different arrays however, and the total number of numbers >0 will not be constant.

I know there are non-VBA ways of doing this, but as I have a large number of arrays to do it would be great to see a macro solution

Any help gratefully appreciated


I am a biochemistry PhD student who is trying to make my research as unbiased as possible by randomly selecting some parts of my data which I can then go on to analyse further.

I have attached this image to help explain my query;

I have a very large data set of individual repeating units, my data set in total is 500 repeats. However, each repeat has a different pixel width and this can vary from 9 - 45 (always an odd integer).

As you can see from my image, I am only concerned with those repeats which have a pixel width of 23 pixels. I use conditional formatting to select these and one of the standard formulas to count the number of cells within range (f:f) which are 23. As you can see, for this sample I have 84 repeats which have a width of 23. (This obviously varies sample to sample).

For my further analysis I only need 50 repeats of 23. But in order to be scientific and unbiased I want to randomly select 50 repeats, rather than using the first 50 or any other manual selection. By 'select' I mean just to choose 50 repeats for me. This could be as simple as highlighting or changing the colour of the cell - the way I use conditional formatting.

I was hoping that I would be able to find a macro that has the ability to do this. But I have never used macros before! So I have no idea where to start, or even what it is I need to be searching for....

If I were to type it into a cell I would hope it to be something like;

="colour"if(f:f, 23)limit=50,randomchoose - if only it was that easy!!

I would be very grateful for any suggestions.


This would seriously improve my quality of life.. Please help

This is my set of data that i need to work with:
Ideally i would like to compose a short list of x(1-12) cells that are randomly selected from this data set, that do not repeat unless all options have previously appeared, and if possible (not necessary) for any item that has the same number in it, not to appear in adjacent cells.

1 A1 B1 C1
2 A2 B2 C2
3 A3 B3 C3
4 A4 B4 C4
5 A5 B5 C5
6 A6 B6 C6
7 A7 B7 C7
8 A8 B8 C8
9 A9 B9 C9
10 A10 B10
11 A11 B11
12 A12 B12
13 A13 B13
14 A14 B14
15 A15 B15
16 A16 B16
17 A17 B17
18 A18 B18
19 A19 B19
20 A20 B20
21 A21 B21
22 A22 B22
23 A23 B23
24 A24 B24
25 A25 B25

i have a database of name addresses, phone numbers, etc, used to create mail
labels for a newsletter. i would like to randomly select a name each week
from this database to give away a prize. i would like to ensure there is no
duplication of names selected.

I have a list of twenty real number in A1 to A20.
How can I randomly select a number from the list, but not the one with
value = 0
If the selected number is zero, it will automatically select another
random number from the list.
The list is dynamic, so I don't know exactly when and where the ones
with zero value show up.
Thank you.


kathyxyz's Profile:
View this thread:


I want to use RAND BETWEEN to pick a random number from a list - and then
save it so it will not recalcutate. My intention is to use it as a way to
randomly select a winner of a drawing.

I tried to password protect the work sheet, but that didn't work. And, I'm
not sure why. Is there a way to randomly select a number, row or cell and
NOT have it recalculate?

Does anyone know how to randomly select a number from a column. I would like to randomly select from a given field, not a random number between two numbers. Thanks

I have a spreadsheet with 552 rows and 18 columns. The spreadsheet contains columns (a few for clarification) last name, first name, badge number, date of issue, badge type (and the list goes on).

I need to perform a 10% random selection audit. I have manually selected 10%. How can I accomplish this with a function or code?

Here is what I have tried (and to make the explanation simple I will use a simple spreadsheet):

I have 3 columns. Last Name, First Name, and Badge. I have tried using the RAND() and RANK() function on the badge number column, which works. But I want to create a second worksheet (Sheet 2) that when the RAND and RANK functions are calculated it pulls the appropriate First and Last name from Worksheet #1.

Any help would be appreciated.

I have over 70 products I sell online. I want to forecast sales by randomly selecting groups of products to simulate an order. Products can be duplicated. I have all my products names, product numbers and profits per product in Excel. Ultimately, I want to show my average profit over a sample of 100 Orders


I have a problem about random selection. I have 6 columns and i need to select one number from each column, but the second selection must be bigger than the first one. For example, if my first column's(assume as column A) random choice is 14, the second's column(assume as column B) choice has to be bigger than 14.

I wrote this formula:


but i failed in implementing the condition into the formula. I attached the excel file, does anyone have any suggestions? Thanks.

ColumnA ColumnB ColumnC
3 4 8
5 13 9
7 2 20
12 15 22

RND--> 5 2 -------> WRONG
RND--> 5 13 -------> CORRECT

Hi all,

I have a list similar to the below:

#.... | Name .... | Weighting %
1 Artur Boruc 1
2 Igor Tudor 5
3 Alessandro Grandoni 3
4 Gian Elia Amoretti 3
5 Olivier Kapo 12
6 Kim Källström 13
7 Emiliano Bigica 13
8 Julio Baldivieso 12
9 Fabrizio Miccoli 13
10 Peter Vougt 14
11 Massimiliano Esposito 12

What I want to do is randomly select either number 1 to 11 (leftmost column OR the person it represents) but also make the selection based on the percentages in the rightmost column. To illustrate using the above example, Number 11 (Massimiliano Esposito) should have 12 times more chance of being randomly selected than Number 1 (Artur Boruc)...

I have used a formula such as
PHP Code: 

to randomly select a cell but how would I modify this to include cell weightings? Or is there a better formula?

An additional note I have is that the weighting for each person will always be changing, but will always be a percentage.

Any help is really appreciated. Thanks!

No luck finding an answer? You could always try Google.