Free Microsoft Excel 2013 Quick Reference

How to pick the random numbers from a set of numbers

Hi all

Im creating a macro which consists of two excel files.
in one excel file, there are four fields named Sno,Name ,Ps ID and Case ID. The user will provide Sno,name and PS ID.
I need to run a macro of which for Each Name, i need to pick 5 CaseIDs Randomly which is presented in a another excel file which has list of CaseIDs in a single column .
I need to check the count of CaseIDs presented in the Second Excel As Well.
I knew that i need to use Rnd() function. But i dont know how to code it since im very new to excel vba.

I have posted this thread in a another forum in the following link:

Plz suggest me

Ganesh. V

Post your answer or comment

comments powered by Disqus
I've just begun using =randbetween, bear with me!

I am trying to come up with a random number from a set of about 100 cells.
The only numbers available in those 100 cells are 0 thru 4.

Do I set my function to choose a random number between 0 and 4
(=randbetween(0,4)) or do I set it to pick a random number from the range of
cells (=randbetween(A1:A100))?

Is there going to be any difference in the data I come up with?

Hello experts:

How to find the largest number in a column and show it in a specific cell ?



How to get the selected textcell from a drop down list?


Ollie Riches

can I delete the 1st number from a string of numbers without adding another
column for the formula?

Hi ,
How to use the Prod Function in a list of averages to find the lease common
value !
here is an example.

3 | These are a list of Over and Under values of the above
3 | These are a list of compile Positive and Negitive values
also average

| trying to locate which one is within range of the
average,one group
| is always known ,in this case it's (25) of the above
values,which now leave (4) items to be measure by an average, maybe if I
could find the Standard Error in the Averaging,it would help in locating(plus
or munis) and the amount !

Thanks in advance



I need to generate a set of 12 random numbers from given numbers 1--24 and I
only want to generate 12 even numbers. Any idea as to how I do this?


So I have a column that that has up to 18 values in it, I would like to take the highest value from that set of 18 cells and have it display in another cell, then the next higest value from the 18 cells in another cell and so on up to five values. How should I go about doing this? Below is a crude example of what I am trying to accomplish.

10_________ 50
20_________ 40
30_________ 30

I am trying to remove the "(All)" item from a PivotTable's filter dropdown.
Can anyone offer any suggestions?

Thanks in advance!



I have attached my excel spreadsheet below.
However, I do not not how to use the vlookup formula from a range of numbers.
Can someone guide me?


I have a set of rowe with info e.g S2345th, I need to remove the 's' for each
row.... without manually edit each row & delete the 's'. Is there a formula I
can use

I have a set of rowe with info e.g S2345th, I need to remove the 's' for each
row.... without manually edit each row & delete the 's'. Is there a formula I
can use


I have a problem to get the right number in userform from textbox:

if textbox1.value = tru then number=textbox1.value

If I enter 2 digits after comma (like 15,22) it is o.k. But if I enter 3 digits after comma (like 15,227) I get the number 15227 instead of 15,22.

Does somebody know how to avoid it?

Thank you very much and have a nice rest of the day.


I'm sure there's a simple explanation to this but i'm struggling. All i'm looking to achieve is to extract the period number from a year/period text string (see attached). I can obtain periods 1-9 by using the RIGHT formula but how can I obtain it for periods 10-12?

Thanks in advance,


I need some help generating 6 random numbers from a list of say 25. I have tried various ways (macro, Rand and Randbetween) but keep on getting dupe numbers. Is there a way to make sure there are no dupe numbers in the list?


Dear Friends,

How to get the total number of Cells (which contatins data) in a specified cell?

Please help.


Working fine. Thanks.

How can I calculate the week number from a date entered into a cel, eg
21/06/05 equals week 26 and so on in Excel.

Any advice would be appreciated.

What I'm trying to do is, I want to remove only the first numbers from a mixed cell, example:

Cell A2 states:
23 pkts x 20 gms
Cell A3 States:
440 cans x 200 ml

I want Cell C2 to say:
AND C3 to say:

I'm using this formula by the way, but its not what i actually wants..
=LEFT(E7,FIND("^^",SUBSTITUTE(" "&E7," ","^^",LEN(" "&E7)-LEN(SUBSTITUTE(" "&E7," ",""))))-1)

What I'm trying to do is pick the lowest numbers from a group of numbers.

5-6 need the lowest 1
7-8 need the lowest 2
9-10 need the lowest 3
11-12 need the lowest 4
13-14 need the lowest 5
15-16 need the lowest 6
17 the lowest 7
18 the lowest 8
19 the lowest 9
20 the lowest 10

hi, im on xl 2003, i have a box with 6 rows where numbers can be added in, but in another cell, i need to know the last number that has been added in this list, ie

1 678
2 456
3 143

so i need cell a8 to keep updating with the last number that has been placed in the range a1:a6, thanks

Does anyone have any suggestions on how to determine the prime numbers?
There is a given number 10 in cell A2, I would like to determine the next
prime numbers below and over this given number 10. In this case, 11 will be
returned in cell A1, and 7 will be returned in A3.
Does anyone have any suggestions?
Thanks in advance for any suggestions

I need a little programme to select randomly numbers from a list of eg. 1-300
and having selected numbers not to repeat them i.e. electronic drawing
numbers from a hat.
Any suggestions?

Julian Marcus

Hello. this is a great forum! Tried to search for my query above but can't seem to find an answer.

My question is, I have a set of numbers, say Euro/GBP exchange rates for the past 2 years. How do I:
1. generate numbers based on this data set and the probabilities attached to these
2. make the generation run by pressing F9

If the above is not possible, I've already generated 5000 numbers based on the occurrences of certain EURO/GBP rates for the past years. How do I then:
1. just take these numbers and generate a number from these and having the number change everytime I calculate?

Any help will be greatly appreciated.


I will be receiving CSV's with a lot of info in each cell but I need the first number that appears in the cell. Is there a way to extract the first number automatically without having to select each one manually? Here's an example...

Here I would need the number 147.23


Programmatically I try to initialize the column width from a pixel
value I have. I found a conversion function but doesn't function quite
accurate. In below code, GetScreenXPI returns 96 on my computer. My
default width is 8.43 char (64 pixel). PioxToCharX in this case would
return 8 char for 64 pixels. What am I missing? Any help would be


' Conversion code

Public Const TWIPS_PER_INCH = 1440 ' number TWIP units per
Public Const TWIPS_PER_CHAR_X = 120 ' number TWIP units per
logical character

Public Function PixToCharX(ByVal pixels As Long) As Long
PixToCharX = ((pixels / GetScreenXDPI) * TWIPS_PER_INCH) /
End Function

Function GetScreenXDPI() As Long
Dim hdc As Long
hdc = CreateICA("DISPLAY", vbNullString, vbNullString, 0)
If (hdc <> 0) Then
GetScreenXDPI = GetDeviceCaps(hdc, 88) 'screen res x
DeleteDC (hdc)
End If
End Function

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