Next Question
RSS
Try this:
Copy the following formula into 100 cells on the current worksheet (or add the worksheet name to the cell references and add it to a different worksheet):
=HLOOKUP($A$1,$A$1:$A$10000,RANDBETWEEN(1,10000),FALSE)
In my example, I'm assuming you have EXACTLY 10,000 rows - if it is more or less just change the "A10000" and "10000" in my formula above to the number of rows in your list.
In each cell this is effectively keying on your first cell and then moving down a "Random" number of rows (between 1 - the first cell - and 10000 - the last cell). It may not be the prettiest solution, but it looks like it will work.
Permalink | Report
'Create the Random function
Function RandCell(Rg As Range) As Range
Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function
'And Use it As you wish
Sub RandCellTest()
Dim Counter As Long
Dim TargetRg As Range
Set TargetRg = Range("A1:A10000") ' The range of values you have
Dim Cell As Range
For Counter = 1 To 100
Set Cell = RandCell(TargetRg)
Cell.Value = Cell.Value + 1
Cells(Counter, 2).Value = Cell.Value
Next
End Sub
Permalink | Report
Answered Question
M$2
September 01, 2009 08:10 PM
Excel: create a selection of random cells
I have an excel sheet with one column and ~10,000 rows. I wanted to select 100 random values from that column and either 1) create a new column with those values or 2) create a new excel sheet with those values.
Thanks!
Thanks!
Interesting Question?
Yes (0)
No (0)
RSS
Best Answer Chosen by Asker
| September 01, 2009 09:02 PM |
Copy the following formula into 100 cells on the current worksheet (or add the worksheet name to the cell references and add it to a different worksheet):
=HLOOKUP($A$1,$A$1:$A$10000,RANDBETWEEN(1,10000),FALSE)
In my example, I'm assuming you have EXACTLY 10,000 rows - if it is more or less just change the "A10000" and "10000" in my formula above to the number of rows in your list.
In each cell this is effectively keying on your first cell and then moving down a "Random" number of rows (between 1 - the first cell - and 10000 - the last cell). It may not be the prettiest solution, but it looks like it will work.
| Asker's Rating: |
• Love it - great, thanks.
Permalink | Report
Reply
Other Answers (2)
September 01, 2009 09:30 PM
If you like a vba aproach you can use this code 'Create the Random function
Function RandCell(Rg As Range) As Range
Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function
'And Use it As you wish
Sub RandCellTest()
Dim Counter As Long
Dim TargetRg As Range
Set TargetRg = Range("A1:A10000") ' The range of values you have
Dim Cell As Range
For Counter = 1 To 100
Set Cell = RandCell(TargetRg)
Cell.Value = Cell.Value + 1
Cells(Counter, 2).Value = Cell.Value
Next
End Sub
Permalink | Report
Reply
Answer this Question
Related Questions
No questions found.
Ask a Question
Buy Mahalo Dollars with Credit Card or PayPal
Top Members
Most Popular Tags
Categories
- Anonymous
-
Arts & Design
-
Beauty & Style
-
Books & Authors
-
Business
-
Cars & Transportation
-
Consumer Electronics
- Coupons Deals
-
Education
-
Entertainment
-
Environment
-
Fitness
-
Food & Drink
- From Email
- From Iphone
- From Twitter
-
Health
-
History
-
Hobbies
-
Home & Garden
-
How Tos
-
Humor
-
Jobs
-
Legal
-
Local
-
Love & Relationships
-
Mahalo Answers Community
-
Money
-
Music
-
News
-
NSFW
-
Parenting
-
Pets
-
Science & Mathematics
-
Services
-
Shopping
-
Social Science
-
Society & Culture
-
Sports
-
Technology & Internet
-
Travel
-
Video Games
Welcome New Members
- troubleiveseen, December 08, 2009 03:58 AM
- ewok, December 08, 2009 03:56 AM
- jeniferbuch, December 08, 2009 03:41 AM
- jtj679, December 08, 2009 03:40 AM
- aud, December 08, 2009 03:39 AM
Mahalo Dollars are the currency of Mahalo Answers.
Each Mahalo Dollar costs $1.
Once you earn more than 40 Mahalo Dollars, you can request to be paid via PayPal. Each Mahalo Dollar is currently worth $0.75 when paid out via PayPal. Learn More
