Mr Excel & excelisfun Trick 11: Randomly Select A Name

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

How do I select multiple rows randomly in MS Excel?

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

Hi

I know excel can generate random numbers,

Can someone guide me how i can create random word generator.

I have list off word in sheet 2 column A, Column B, Column C and Column D from row 1 to 60,000.

In sheet 1 i want to press next button and word is selected form sheet 2 and displayed in a cell BUT no word is displayed again if it was displayed once.

I am creating this file for school as one of the teached wants to do spelling test on kids, i though a file would make it easier.

hope someone can help out.

Hi

I am looking for a formula to select 5 random cell entries from a list in column A in Excel and put them in 5 cells say in column B.

I have a list of resturants in column A, and I want to select a random resturant to go in 5 cells, but a resturant can only appear once in those 5 cells.

Can anyone help?

I use INDEX and MRAND functions (copied from Excel'sFun) to draw teams of 4 from list of players entered for a golf competition. This works well but I need to ensure the Captain is first out of the draw.

Before finding MRAND I used to use RANDLOTTO and a VLOOKUP but it was convoluted.

Any ideas on how to overcome my problem?

Please be aware that I am keen to learn but slow on the uptake!

Good afternoon,
I have code below that will move the entirerow to Sheet2 if column A has an x in it. I am looking to adapt this code so that I could randomly select 96 people with out having an x in Column A. Any help will be greatly appreciated! Thanks in advance Stephen!


Code:

Sub moveit()
Dim r As Range
Set r = Range("a1:a3000")

For Each cell In r
    If cell.Value = "x" Then
        cell.EntireRow.Copy Sheets("sheet2").Range("a65536").End(xlUp)(2, 1)
    End If
Next
End Sub

Hi, I'm back with another one.

I'm trying to pull 20 random names from a list of them every time I open the spreadsheet, and have them copied onto the clipboard to be pasted wherever. I have some code I found to accomplish this, but I'm not exactly what you would call VBA savvy. Could anyone look at this and see what I'm doing wrong? Or if it is right what I'm doing wrong to get it to work? Any help would be so appreciated. I know this is simple, but I can't figure out how to get it do what I've asked it to.

Sub GetRandom()
Dim iRows As Integer
Dim iCols As Integer
Dim iBegRow As Integer
Dim iBegCol As Integer
Dim J As Integer
Dim sCells As String

Set TempDO = New DataObject

iRows = Selection.Rows.Count
iCols = Selection.Columns.Count
iBegRow = Selection.Row
iBegCol = Selection.Column

If iRows < 16 Or iCols > 1 Then
MsgBox "Too few rows or too many columns"
Else
Randomize Timer
sCells = ""
For J = 1 To 20
iWantRow = Int(Rnd() * iRows) + iBegRow
sCells = sCells & _
Cells(iWantRow, iBegCol) & vbCrLf
Next J
TempDO.SetText sCells
TempDO.PutInClipboard
End If
End Sub