Randomly Select Value Meeting Criteria From Table
Jan 17, 2007
I have a table detailing products and product options showing the options possible for each product. Each row has an option class and value(in separate columns):Color Red,Color Orange,Color Blue,Style A Style B and so on. Each product is in column.
Valid options for a product are indicated with a 1, invalid options with a 0. See attached sample file.
For instance, if the product in question is Alpha, then available colors are red and blue.
For a given product I need to return a randomly generated valid option. As I want to be able to drop in additional products and options, I want to return a value without resorting to manually generating a column containing only the valid options. The real data set amounts to dozens of options types and hundreds of parts for which I want to generate test data.
View 6 Replies
ADVERTISEMENT
Oct 31, 2013
I am trying to randomly select 10 data from a table by using index formula. But there are some blank cell in the table so It kept returning 0 in the cell.
problem.xlsx
View 2 Replies
View Related
Apr 10, 2008
My goal is for any of my team members to be able to use a macro to delete all of the unwanted text from a fairly large worksheet and only show their specific information. I created it for myself and posted a slimmed down version of it below. Instead of the macro deleting any rows that do not have BRANDON SCOTT in column D, I want a pop up box to enter your name, and then any rows that do not have your name in column D will be deleted. I’m sure it is something simple, but I have not been able to find another forum with this information.
Sub MyTeam2()
With Columns("D")
. AutoFilter field:=1, Criteria1:="<>BRANDON SCOTT*"
.Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
.AutoFilter
End With
End Sub
View 3 Replies
View Related
Apr 11, 2008
I have a workbook with sheet 1 called Support which is basically an input form. The second sheet is called Database.
I want to enter data into the form and via a macro copy the data to the database. I have followed a text book to the letter on how this is to be completed to the letter.
My problem is that when the data is written to the database some of the fields are blank. Using F8 I can step through the VB script which appears to select fields at random.
View 10 Replies
View Related
Apr 17, 2009
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?
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
View 9 Replies
View Related
May 14, 2012
I have a column of DV lists. Is it possible for me to make excel, go through each DV list, and randomly select an item from each DV list?
View 1 Replies
View Related
Feb 15, 2013
The macro below will randomly pick a cell (with names)and fill with color. What I want to add is after it pick a cell a msgbox with a name of that person with YES/NO, if YES fill color. I need it not duplicate once it has already selected that cell(person).
Code:
Function RandCell(Rg As Range) As Range
Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function
[Code]....
View 9 Replies
View Related
Oct 16, 2013
I am wanting to randomly select one of two cells and then populate that cell from a list I have on a different sheet.
View 1 Replies
View Related
Oct 18, 2006
by what means is it possible to unselect randomly selected areas, rows, columns from all sheets?
View 4 Replies
View Related
Nov 6, 2007
I have a sheet that has 13 players listed in rows down column A. I have 6 columns each representing a quarter in a game. I was wondering what the best way would be to randomly select 5 players in each column and to distribute each cell selection as equally as possible over the 6 quarters.
So what I am doing is having excel designate equal amounts of playing time for each player each game.
Is this possible.
Can I base this off of previous games?
View 9 Replies
View Related
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
Jan 8, 2008
I want to randomly select letters from this distribution (to make random words) but I want the more frequent letters in the distribution to be selected more often so that the word I form have the same distribution. I have the feeling there is a simple solution to this but I couldn't see anything other than the frequency function that I thought I might be able to use. I could populate an array with letters present in numbers according to their frequency and then randomly select from it but is there a more elegant solution? .......
View 10 Replies
View Related
Mar 31, 2014
I have two tables (Table 1 and Table 2). Table 1 has names of all employees in my organization ('Name' column) and corresponding information in the second column ('Textinfo'). I need to create Table 3 from table 1, but only select those employees who are in my team, ie matching names in the Table 2. I also need to extract corresponding 'Textingo' column information. I tried vlookup but it did not work because if there more that one name in the table 1, vlookup confuses it (i think). Would you know what function or code to use?
Sample file is attached : excel question 1.xlsx
View 2 Replies
View Related
Jul 17, 2014
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company
-------------------------------------------
1/1/11 Jane A Company
1/1/11 Jane B Company
3/1/11 Bob B Company
2/1/11 Bob B Company
4/1/11 Bob B Company
5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
View 1 Replies
View Related
Jun 20, 2014
I'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
View 3 Replies
View Related
Nov 12, 2012
I'm doing an assignment. This would be very simple w/ filters but I have to do it thru formulas.
I have a bunch of rows w/ various hotels. Row 14 is Rolling Meadows, Row 15 is Lakeview Apartments etc.
Near the top I have to add the number of times Rolling Meadows is mentioned, in the cell below Lakeview apartments, etc.
I tried a sumif formula but that doesnt work. How would you do this? This is what I wrote, I got 0
=SUMIF(B14:B26,"Rolling Meadows")
That's part 1. In part 2 I have to calculate the number of units in that building that are occupied.
View 9 Replies
View Related
Mar 26, 2013
we would like to get results from a formula that looks at several cells and provides the cost for a product.
Example
If we choose
Cell A3=Transport (from drop down list)
Cell A4=Entrance Facility (from drop down list)
Cell A5=Bandwidth (from another drop down list)
returns the cost for this product in cell A6
We would also like to restrict the lists to the different catergories: if transport is selected you only have the option of 2 of 5 facility types that will work with transport products. Do I need to separate my lists?
View 1 Replies
View Related
Oct 6, 2006
I need to get the sum of a column where two conditions have to be met. I have tried doing something like: Sum(Sum(AND(V1:V1500="BEBLT", H1:H1500=17) +-Sum(AND(V514:V1512= "NVLIV", H514:H1512=17)))
In a nutshell I want to find the difference between the sum of a column whose criteria is 17 and BEBLT and the sum of another column whose criteria is 17 and NVLIV. This has been extremely frustrating since I am using SQL commands to pull the info from the workbook. Typically I would use
"Select SUM(Z) from Sheet1$ WHERE (H1:H1500=17 AND V1:V1500);"
Unfortunately for me my SQL command brings back an error so I must resort to finding the total in excel and then using the command to bring it into my VBA.
View 4 Replies
View Related
May 27, 2007
I'm trying to use the SUmif with Offset, but the width argument of offset doesn't
seem to work within the SumIf function. I have 14 columns
A Employee
B department
C-N jan through dec salaries per employee per month
41 rows
1 titles
2-41 names of employees
now in May I want to know the salaries per department for the month may only, but also cummulative from jan up to may. Getting may per department works fine, but getting the cummulative doesn't seem to be working
=SumIf(B2:B40;"account";offset(C2;;4)) gives me the sum of department
Account in the month of May
=SumIf(B2:B40;"account";offset(C2;;;;4)) starting point zero (= january) plus 4
columns width, just gives me January.
I've tried several options, but every time he only sums 1 column. Without the SumIf it works fine but for the whole company, not per department ofcourse.
View 4 Replies
View Related
Jun 9, 2007
how a SUMPRODUCT function can resolve this. In this sample I want to have 1120-32188 & 1120-32188W be treated the same for the Total sum qty. In this case =10.
This will compute but how do I include items in column A that have 'W' suffixes for computing Total Qty? Formula: =IF(A2=A1,"",SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100)))
View 4 Replies
View Related
Aug 9, 2007
I have an Excel sheet with 3 worksheets. On the first sheet I want to enter a formula that will check the third sheet. On the third sheet I need to compare all the entries in column N with a value in Cell B2 of the front sheet. If these match then I need the formula to add up the relevant values in column N. (column N has a 4 letter code, I am only interested in comparing the first letter, as such the value in cell B2 is H*, then in column U it has numbers of minutes, I want a total of the minutes of all those entries with a code that begins with H)
View 4 Replies
View Related
Mar 27, 2008
What I have is 4 columns of data broken down as follows Column A = Date Range B = Name C = City D = Amount of People.
What I want to achieve is to be able to go through my list of data and for all rows that match specifically matches A,B,C it will total column D and output to a cell. I also need for it to reflect the name somehow .. so in attached example A2-C2 matches A8-C8 so I would need the output of D8 + A8 (which should be 54) but I need for this to some how reflect a name combination City + Name. So I would know that abc & California have 54 people and that def and New York have 56.
I have attached a basic spread sheet of data; keeping in mind that in the complete data there could be hundreds of combinations for data to be matched.
View 8 Replies
View Related
Nov 6, 2009
On the attached file, I would like the values from the Transactions worksheet to show up in the appropriate cells in the XYZ Expenses worksheet. The catch is, I would like the XYZ Expenses worksheet to only show the values from the past week. Past week could be literally, but I'm guessing that might not be possible and it would have to use the past week from the most recent transaction date - which would be fine. So if it worked right, XYZ Expenses > F7 would show -$80, which would be from Transactions H3+H6 (and not -$130, from Transactions H3+H6+H9).
View 3 Replies
View Related
May 7, 2005
Problem:
Range A2:C10 contains the login and logout times of various ID's.
Each ID could log in and out a number of times a day.
How could we find the first time a specific ID logged in and the last time that same ID logged out?
Solution:
For each of the ID's in range A2:A10, enter two Array Formulas.
To find the first login time (Column B) enter the following formula:
{=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10
To find the last logout time (Column C) enter the following formula:
{=MAX(($A$2:$A$10=A14)*($C$2:$C$10))}
ID______Login Time______Logout Time
1 ______02:40___________03:10
2 ______00:15___________03:20
1 ______06:20___________09:30
3 ______09:14___________11:05
4 ______11:00___________19:30
2 ______04:05___________06:55
3 ______12:08___________17:17
1 ______10:00___________16:20
2 ______08:12___________12:33
ID______First Login Time______Last Logout Time
1 ______2:40__________________16:20
2 ______0:15__________________12:33
3 ______9:14__________________17:17
4 ______11:00_________________19:30
View 9 Replies
View Related
Jan 1, 1970
I can get the result. It shows #num! in cell F2
View 14 Replies
View Related
Jul 25, 2012
A column W5:W9999 contains attendance from either of 0, 1, ........31 of employees.
How to count no of employees meeting certain criteria like:
Total no. of emp with values >0.
Total no. of emp with values >0 but < 16.
View 9 Replies
View Related
May 11, 2008
I have been having problems all morning and have not been able to solve it!
I am sure this is very simple....
What I would like to do is assign the result of a sum to a variable. This part is easy what I cannot fathom or find any help with is the multipule criteria bit!
Here is the detail.
Column A - Employee Number
Column C - Area
Column F - Type
Column D - Amount paid.
I would like to sum the amount paid based on the 3 criteria, Employee Number, area & type and assign this to a variable to be used later in a sub I'm playing with.
View 9 Replies
View Related
Apr 21, 2009
I want to write a sub that captures the existing lists in the attached file in two arrays then create two new arrays of customer names and amounts spent for customers who spent at least $500. After these arrays have been filled, I want to transfer their contents to the columns D and E.
View 3 Replies
View Related
Sep 28, 2006
im trying to set my workbook up so that it is updating itself daily. Currently it has about 4000 rows of invoices from multiple departments. What I want is a formula that when an invoice reaches 31 days that it goes to a separate worksheet. I don't want it deleted, I just want it to be moved in to a worksheet called 'WatchList'.
Any body know how this can be done. What Ive done right now only puts the row in the same row from the main sheet. So if row 2000 reaches 31, it shows up on row 2000 in 'WatchList'. Im wondering how I can move row 2000 to the top and whatever other row to the top once it reaches 31.
View 4 Replies
View Related
Nov 1, 2006
I would like to seek help on how to delete a cell that has data in it and that has colourfill. E.g. Cell A1 shows "Occupied" and Cell A1 has a blue colour fill.
Need help on how to delete that cell's data and remove the blue colour without having to manually do so.
View 4 Replies
View Related
