Random Select One Line Of Data For Each Person In A List

Dec 20, 2012

I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but I would like to try it on a large set of data. I have been able to get a random transaction but not one for each person.

Code:
a1
b
c
d
e
f
g
h
i
j

2

3

4
REF_ID
NAME
DATE
BILL TOTAL

[Code] .....

View 1 Replies

Random Item Per Person

Dec 19, 2007

I have a list of order numbers and sales persons associated with those order numbers..

I was trying to come up with an idea of how to enter a Sales Persons Name in a field and have a random order number based on their name populate so that I can audit that particular order.

Can this be done in excel with a vlookup formula or something to that affect?

And if so, how?

Order # Sales Person
256796 Judy
248986 Brian
897655 Fred
245466 Judy
364599 Tomas

Since Judy has more than 1, I would like to just radomly pick one of those... Now, just a little side information, my actual sheet has approximately 3000 orders with 52 sales reps...

View 9 Replies View Related

Select Random Number From List

Oct 23, 2009

Say I have a list of 100 numbers (in column A). What formula would I use to have Excel list (in column B) 50 random numbers from the list of 100 or column A?

View 6 Replies View Related

Select 30 Random Items From A List

Apr 30, 2014

I've got a list of items (formatted alphanumeric) in column G starting at row 2. I would like a cell formula that would randomly select 30 items from this column and place them in column H starting at row 2. One caveat is that the list must remain static once created. If the Rand()function is used, it recalculates whenever the worksheet is updated so that might be a problem.

Cell formula to do this? A VBA solution would be OK as well.

View 9 Replies View Related

Line Graph With 2 Lines Per Person?

Sep 15, 2013

I need to create a line graph that has 2 lines for each person, one with the amount, one with the points. The X-Axis is by Weeks going up. How can I do this without making a separate series for each? I've attached an example.

example.xlsx

View 1 Replies View Related

Random Generator, Change A Line Or Two

May 17, 2008

I have a random Generator but it only generates values that are different, is it possible to change a line of code to allow it to duplicate the same number? Here is the code I have and it works great but not for duplication:

Sub Unique_Numbers()
Dim x As Long, y As Long, z As Long, tempnum As Long
Dim flag As Boolean
Dim i As Integer
Dim foundCell As Range
Application.ScreenUpdating = False
x = Application.InputBox("Enter starting Random Number" _
, "Random Number Generation", 1, , , , , 1)
y = Application.InputBox("Enter ending Random Number" _
, "Random Number Generation", 1000, , , , , 1)
z = Application.InputBox("How many random numbers would" _
& "you like to generate (

View 9 Replies View Related

Report A List Line By Line Based On Name Criteria

Jun 21, 2008

Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.

I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):

This would be the data on sheet 1:

Name Bonus Commision

Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25

What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:

Tom's Sheet:

Tom $45 $50
Tom $60 $50
Tom $90 $25

Mary's Sheet:

Mary $25 $75
Mary $30 $80

I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.

Is this possible to do in Excel without using a macro?

View 9 Replies View Related

VBA Random Name Select

Dec 12, 2012

I have been thinking about a name selection tool that would automatically pick a few names for internal audit. I can handle the useform launch and other basics but here is what I see happening.

1. The empname! sheet contains a list of all the company employees, with employee number in column A, names in column B and an "X" or blank in column C.

2. The main sheet called auditmaster! contains nothing currently

3. When the code is ran the userform prompts the user to select how many individuals to audit by entering an integer in a textbox.

4. If the user selects 5 for instance, the code will run and will select 5 random employees for internal audit. It does this by first checking column A of the empname! sheet and seeing what the highest and lowest numbers are. This would create the range for the random function.

Next if there is an "X" in column C of empname! sheet, those employees numbers are excluded from the random choice.

Following this the function will select the appropriate number of individuals to audit using random again and place their names/number on the masteraudit! sheet.

The detail should be placed as the following, employee number goes to column F, name to column G. Starting with row 10. The code should also number the results 1 - however many were selected for audit in column E.

View 2 Replies View Related

Random Select.

Sep 14, 2008

I want to random select one of the following,red,yellow,blue,green,orange & white. (as text) in cells A2:A1001.

View 9 Replies View Related

Random Number Select

Feb 5, 2007

I need a macro that will select a random number between 1 and 6. ie select the number of a dice. It needs to input 1 random value into one cell and another random value into another. Like the dice values of a monopoly game.

View 9 Replies View Related

Select Cell At Random

Dec 10, 2007

I have an excel sheet attached that I would like to customise to become a random seat picker. I wanted it to select a random cell representing a seat and highlight that cell in someway (e.g. change the background colour or text colour) and display the data within in in a cell in the middle of the sheet.

I have used formula to achieve the later half of the problem but am unsure how to highlight the cell. my current solution uses f9 to refresh the data. Ideally I would like to attach the behaviour to a command button. I have attached the excell sheet and have highlighted the problems I am having.

View 3 Replies View Related

How To Select 50 Random Values From Sheet1

Dec 13, 2013

How to copy and paste 50 values randomly from column A in sheet1 to Column A in sheet2.

View 5 Replies View Related

Random Function On Select Range?

Mar 14, 2014

I would like to use the random function on a range of select numbers. I tried randbetween() but it won't work for me since my range of numbers are not in sequence (e.g. 1,2,4,6,7,8,9,10,21). Using randbetween() might result in numbers not within my range..

View 2 Replies View Related

Select Random Cells In The Selection

Feb 8, 2009

I'm trying to select an x number of random selections within the selection.

Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them

This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?

View 9 Replies View Related

Relating List Of Random Numbers To A List Of Names To Create Rota

Nov 21, 2013

I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).

View 3 Replies View Related

Select 81 Winners At Random From 5000 Entries

Nov 26, 2013

I am looking at ways to select 81 winners at random from a spreadsheet with 5000 + names on it. I have tried assigning each cell with a value between 1 and 81 and then removing the duplicates but i cant seem to get excel to pick 81 unique numbers, instead it will pick no number 1, or two number 70s.

I have managed to get a macro to pick 1 cell at random but cant get it to pick across the whole range of 5000.

View 1 Replies View Related

Select Random Rows And Display On New Sheet

Mar 30, 2009

I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.

Here is the original code from the previous post.

View 14 Replies View Related

Select Random Row, Split Cells To Different Worksheets

Jul 26, 2009

I am trying to write a macro to:

1. Randomly selects 50 rows from 834 on "worksheet 1". There are only two cells per row.

2. Then, it clears "worksheet 2" and "worksheet 3".

3. From each selected row in "Worksheet 1", it splits the two cells and copies the results into two different worksheets, one column per sheet.

4. The rows in the two new worksheets should be congruent (i.e. same selected order from worksheet 1). Keeping the order the same is important.

This is what I have so far:

--------------------
Sub Macro1()
Dim rng As Range
Dim iRow As Long
With Rows("1:834")
Do
iRow = Fix(Rnd() * 834 + 1)
If rng Is Nothing Then
Set rng = .Rows(iRow)
Else
Set rng = Union(rng, .Rows(iRow))
End If
Loop Until rng.Areas.Count >= 50
End With

rng.Select

Selection.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

End Sub
-------------------

View 9 Replies View Related

Send Each Individual Person Their Own Data - Create A New Tab

Aug 7, 2014

I have a list of data which i need to send to individuals. I can sort the data by person's name.

I need to send each individual person their own data. Instead of copying and pasting each person's data into a new tab for everyone, is there a quick way in excel that for each change in person's name (Column A), that it creates a new tab with all the data?

View 5 Replies View Related

Data Validation - Auto Select From List

Jan 17, 2014

I have data validation in a cell that says only options from a list are allowed. i.e. =indirect(A1)

If this refers to a list where there is only 1 option available is there a way to display that value automatically without the user having to click the dropdown to select it?

View 1 Replies View Related

VBA To Select Next Item In Data Validation List

Feb 28, 2014

I wrote a macro that creates a data validation list from a set of cells on Sheet 1.

On Sheet two I would like a have Macro select each item in order on the validation list and paste into every fourth cell.

For example, if my Data Validation list on Sheet 1 contains three items: Comp. A, COmp. B, Comp. C

On Sheet 2, I would like to see Comp. A in cell K1, Comp. B in cell O1, Comp. C in cell S1.

I have seen a few codes but nothing that works effectively.

View 4 Replies View Related

Data Validation Which Allows To Select Values From A List

Jun 8, 2006

I have data validation in cells A1:A50 which allows to select values from a list. There are about 50 items in the list that can be selected. Is it possible to have the user select an item only once i.e., if an attempt is made to select an item thats already selected it show an error message.

View 3 Replies View Related

Optimize Select Random Cell Value Without Duplicates Code

Apr 6, 2013

I have two worksheets. ws1 contains a large number of dates on column 3. ws3 contains around 20 dates on column 3.

I want to pick two dates randomly in ws1 that does not already exist in ws3.

Here is my working code, but it is really slow to process. How to optimize this code? Also I would like to add a function where if the macro is not able to find two random dates that aren't already in ws3 then exit loop.

VB:
Set ws1 = thisworkbook.sheets(1)
Set ws3 = thisworkbook.sheets(3)

lr = ws2.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
Set rvis = ws2.Range("c2:c" & lr).SpecialCells(xlCellTypeVisible)
mynodate = 0

[Code] .....

View 7 Replies View Related

Select Random Cells From Multiple Sheets And Copy

May 10, 2014

I would like to select say 2 id from sheet1 and 2 names from sheet 2 randomly and copy to sheet3, to cells a and b,have seen various codes but none seem to fit the bill.

View 3 Replies View Related

Select Multiple Items From Data Validation List?

Feb 4, 2014

I have created a DAta Validation List and it only select one item. I want it to select as many items that is on the list but i can't get it to work for my main worksheet I am working on. I can't figure out why I can't get it to work for the work sheet "This Don't" but it works for the worksheet "This Works" why and how I can change it so both Reason (Column F and H can) select mutliple items on there. Data Sheet.xlsx

View 2 Replies View Related

Randomly Select Varied Amounts Of Data From A List

Sep 10, 2008

I have a list of people in column A and a list of Cities that they have visited in column B.

I need to check some of the cities they have visited monthly but don't want to check them all.

I have attached a sheet as an example (this has been scaled down).

The number of cities i want to check for each person varies each month depending on how many cities they have visited.

For example, John has visited 16 cities and i want to check 5 of them. I therefore want 5 random cities that he has visited to appear next to his name at the top. The real list of data is massive so this would be really useful if it is possible.

I have looked at rand but i can't get it to randomly give me more than one city, and i don't understand how to get it to give me say 5 cities one month and say 8 cities the next month purely based on a formula from another cell.

View 9 Replies View Related

Data Validation List To Select Worksheet In Macro

Apr 8, 2007

I have a Data Validation list in cell A1 of a worksheet called LIST. I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded. THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.

View 7 Replies View Related

Dropdown Menu To Pull Data For 1 Person At Time From Table?

Apr 29, 2014

Drop down menu that can pull data for 1 person at a time from Table?? (Even possible)

View 1 Replies View Related

Pull Data From Others Sheets Using Drop Down List To Select Sheet Name?

Sep 29, 2010

I'm not including my spreadsheet because it has company information however, I will try to articulate my request as best I can. I currently have an Excel spreadsheet with about 20 worksheets, I would like to be able to click on the first worksheet, create a "Drop Down box" or "Data Validation" box or whatever I need to do to be able to view information in a range of cells on ANOTHER worksheet within the same workbook.

I've tried Data Validation, Drop Down Boxes, Define name ranges, all to NO avail. I'm missing a step somewhere, because I'm still not able to view the information on the first worksheet.

View 3 Replies View Related

Select Multiple Items From Excel Data Validation List?

Jun 7, 2014

I watched a video on youtbe. And the women had three or four items in drop down box. She was using vba. When i emailed her to ask her how she did this, she kept telling me to go to her website and i was supose to figure it out on my own. I am trying to do a start up construction company. the link to her site is as follows Select Multiple Items from Excel Data Validation List - YouTube the idea of what i want to do, is how she clicks on a word and it follows in the coloumn to the right going down. This is what i need to know how to do. It is probably pretty elementary to many of you. But i build, i know how to read prints, to cut footprints for new homes etc. But when it comes to excel i am so green.

View 2 Replies View Related