Generate A Random List In A Column That Adds Up To A Fixed Amount
i had a basic spreadsheet for my incomings and i got wiped with my hd exploding!! i know how much the total was i just need a way of creating a list of random amounts within a specified range over a year to give to my accountant.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Generate Random Numbers From A Frequency List
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
View Replies!
View Related
Generate Random Numbers In Random Cells
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
View Replies!
View Related
How To Generate Random Text
I was just wondering if there is a way to make rand or randbetween functions work with text. Alternatively, is there another function that will allow me to do this, without adding numbers in the parallel row. I want the column, to randomly choose between "male" and "female". I do not want to add additional rows or columns preferably..
View Replies!
View Related
Generate Random Number
is if there is a way to generate random numbers between 0001 and 9999. I always need to always have 4 digits. The first couple of numbers can be 0 so that I can maximize the number of variations.
View Replies!
View Related
Generate Random Numbers Between 0-9
I am trying to generate random numbers between 0 - 9 in ten cells that do not repeat an always include every value 0 - 9 (0, 1, 2, 3, 4,...9). The object is to have the numbers randomize each time. So cells A1:A10 could look like this: 3 9 4 5 1 0 6 8 7 2 Or, they could look like this: 9 7 5 2 8 4.................
View Replies!
View Related
Generate Random Number Between
i have worked in access using vba procedures. i can easily create a form in access that generates a random integer number between 0 and 20 and the user will guess the number between 1 to 20 if the random number is 16 and the user enters 14 the program will display a msg box saying too low or too high. here is the code . Option Explicit Private Const MAX = 20 Private Const MIN = 1 Private iKey As Integer Private lTries As Long Private Function Random() As Integer Random = CInt(Int((MAX - MIN + 1) * Rnd() + MIN)) End Function Private Sub cmdGuess_Click() Dim lGuess As Long now i have to do the same thing in excel create a program that will ask the user to enter numbers between 0 to 20 and see if the random number and user guess is same or not. but i have never worked in excel using vba procedures how can i achieve the same thing in excel using a vba procedures.
View Replies!
View Related
Generate A Dataset (random Numbers, Mean, Etc)
I'm trying to generate a dataset to use as an example in a class I'm helping out with. I want 75 cases (that is, 75 rows) Column A randomly pick 1 or 2 Column B 60% 1 and 40% 2 randomly assigned to cases 1-75 (Thus, 45 1's and 30 2's randomly assigned to rows 1-75) Column C Randomly pick from the range of numbers 18-65. Make sure the mean for the column is 48.72. Column D Randomly pick from the range of numbers 1-7. Make sure the mean for the column is 5.49.
View Replies!
View Related
Generate A Random 9 Digit Number
I have a spreadsheet with approx 11,000 rows and I would like to generate a unique 9 digit number for each line. I know I could just put 100 000 000 and then increase that by one all the way down but I was wondering if there was any formula or code
View Replies!
View Related
To Generate Some Random Problems. But The 1milion
name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"> Generate random problems In addition to data from af directory i got it must be possible to solve some home-made problems. Therefore i need to make a program that can generate new problems in Excel - likely on the same sheet used for the imported problems (another code i got). It should be possible to specify the size of the problem, in other words the number of cities. Like the problems from the same directory. The problems i am generating have to consist of a number of cities with its x-y-coordinates. It will be useful if the program or the user sets a minimum and maximum value of the allowed x-y values so they are within a certain interval. Do i need a very big code for this?
View Replies!
View Related
Generate Unique Random Numbers
I want to generate numbers (1 to 15) in cells A1 through O1, but the number in each cells should be unique compare to the other cells, how can I do it? If I use =randbetween(1,15), I can not get unique number in each cell, some numbers are duplicated.
View Replies!
View Related
Generate Random ID & Increment Others
I'm using Excel to generate a userid based upon criteria (e.g. IF column A = 2, and not in array(another sheet), use a random id). This works, but I want an incremental id (whether already used up). I am using =MAX(IF(LEFT(Users!B1:B19900)="2",Users!B1:B19900)) to get the highest number in the array ...
View Replies!
View Related
Generate X Random Numbers Adding Up To 1
I've got a problem with creating random numbers for a fictional portfolio. I've to have a row of random number but they have to add up to 1. Example: Need random numbers from B1-B11 under constraint: Sum(B1-B11) = 1 I tried to do it with the solver addin, but it only takes simple numbers as a constraint.
View Replies!
View Related
Button To Generate Random String Of Numbers
I've been using excel for quite sometime but have never tried the macro system or posting VBA code so I'm wanting to get my feet wet with a little project. I want to make a button on a sheet that when pressed generates a random number between 3 and 18, ideally it would make a new set of random numbers if pressed again.
View Replies!
View Related
Finds Duplicates In Column And Adds An Alpha Character
I have a Macro that finds in Column F duplicates and adds an Alpha Character (A-Z) to the last right position in the filed.(1-12 Alpha/Numeric) . It's set to check all rows until a specific number of rows are reached. The current code does this until intCount and intRow equals 1500. The number of rows vary in each file.
View Replies!
View Related
Using A List In A Pivot Table To Generate A List On Another Sheet
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A). on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity. What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.
View Replies!
View Related
Drop-Down List: Generate A List Based On The Value Of A Cell
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15 A drop-down list displays the values 1 through 15. Cell A1 changes to 20 Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
View Replies!
View Related
Find A PORTION Of Text In A D Column And Automatically Adds The Price To The Right Of It Together In One Cell
I am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase. My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks
View Replies!
View Related
Fixed Column Width
I'm trying to create a vb to get all columns on my sheet to have a fixed width. If I run my sub all columns go back to the set width. But I want the width to reset when the width is changed.
View Replies!
View Related
Auto Generate List
I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day). There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending. I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header. The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)
View Replies!
View Related
Generate Three Different Results From One List
Find attached an example sheet. I have one list with data... Name, Handle, Result. I want to have three lists from that data List 1 would have top ten highest results List 2 would have top ten lowest results Some results will be the same so I would need each Name listed as they appear in the main list until ten is reached. List 3 would have top ten handle, but would not include anyone in list 1 or 2. This will be a shared workbook
View Replies!
View Related
Generate Sheets For Each Name In List
I have a long list that is geneerated each week and I need to split them based on their category acrross a number of sheets all named by the category. These sheets don't exist at the beginning. My list would be like: Col A Col B UK Dave UK Bill UK Ann US Bob AUS Sheila AUS Bruce I want to run a macro down the list and build sheets for each unique ColA ie UK, US and AUS and then copy the ColB values into the correct sheets so UK has Dave, Bill and Ann in colA of the sheet named UK. anyone have an example of this type of process.
View Replies!
View Related
Generate New File For Each Entry Of A List
I have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.
View Replies!
View Related
Generate A List With The Date Of Every Other Friday
On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used: Private Sub UserForm_Initialize() ' Populate the ListBox control. listPayPeriodEndDates.AddItem "04/03/09" listPayPeriodEndDates.AddItem "04/17/09" listPayPeriodEndDates.AddItem "05/01/09" listPayPeriodEndDates.AddItem "05/15/09" listPayPeriodEndDates.AddItem "05/29/09" listPayPeriodEndDates.AddItem "06/05/09" listPayPeriodEndDates.AddItem "06/19/09" listPayPeriodEndDates.AddItem "07/03/09" listPayPeriodEndDates.AddItem "07/17/09" listPayPeriodEndDates.AddItem "07/31/09" listPayPeriodEndDates.AddItem "08/07/09" listPayPeriodEndDates.AddItem "08/21/09" listPayPeriodEndDates.AddItem "09/04/09"........................ What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).
View Replies!
View Related
Generate List That Match Criteria
I have been working on a project with 30 products and 20 different companies. I am working on a business case, where there are launch dates for various companies with expected sales, contribution margin, NPV etc. The idea is that I can create specific scenarios (What is Company1 doesn't launch the products, etc), and there is an accompanying each company. Because it can be difficult to see what products are included in the current calculations, I would like to use some kind of function that produces a list that matches my criteria. (In this case, all cells for a company that are larger than 0). I would like to find a function that finds all products that have a launch date and create a list of them -- without spaces between non-matched results. And that it updates whenever data is changed (new launch year or new product in company's profile) I have included a generic excel file to show what I am going for.
View Replies!
View Related
Generate Drop Down List From Another Sheet
I am trying to create a list, I know how to do this on the same sheet, I need to be able to have the list informaiton on a data sheet that contains other information that I need to have to populate other parts of the sheet. then I need to add this list data so that I can have drop down options so that users can pick from selected work descriptions
View Replies!
View Related
Format Column Widths - Free And Fixed
I need to know how to format columns in a spreadsheet. What I want to do is autofit the columns, all except for Column A; I want that want at a fixed width of 9. The code below is my attempt to do this, and it's not resulting as I wish. Stepping through the code, it performs the first part beautifully and autofits all of the columns. Where I have the line
View Replies!
View Related
Filling Data Down A Column With Fixed Values?
I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?
View Replies!
View Related
Multiply Column Of Numbers By Fixed Number
I did a search myself, then went through the 5 pages the "Post" function brought up. I'm sure it's been asked before, I must just not be using the right words I need to tell every cell in one column to multiply itself by a firm number and a percentage. Ex: Column F will be the result of $77.80 times 75 percent all the way down the column. Column G will be $27.21 times 100 percent all the way down. I could enter the formula into every cell, but I wondered if there's a way to just tell the whole column, only once, what it needs to do. PS-I work for an insurance company. The employees of a company want a spreadsheet showing exactly who pays exactly what. Ex: Their paystub shows $142.90 coming out of their paycheck. Their employer pays 100% of medical, 75% of dental. The columns would show Total, Employer Medical, Employer Dental, Employee Medical, Employee Dental.
View Replies!
View Related
Generate New List And Tally Non Blank Cells
I am trying to make a more useful excel extracted report – what I have to start with is muck like the attached sample file. With at the bottom is what I would like to be auto done. There is a column of names – with most names repeating so the first thing needed is to create a new summary list – can be in the same worksheet or in a new worksheet. The new list with be just a list of each name but only display each name once rather that the multiple of times as source list. The second requirement is than once have a list of each name that appears then in columns next to each a tally /count of each non blank cell under each respective column heading - now it has to be a count of non blank cells as the content of each cell will chance – now need to understand it is not a count of numerals as such but a count of non blank cells so it is not 1 + 3 + 5 = 9 but should be 1 +3 + 5 = 3 (3 non blanks). A idea of what I want is at bottom of sample worksheet attached
View Replies!
View Related
Random List
i have entered some values in excel like this A1 B1 1 a 2 b 3 c 4 d etc i will put a command button, like "Randomise" what i wanted is , if i click that command button, the Value in cell A1 should be remain same. but the values in B1 has to be randomly changed that is, eachtime i click the randomise button the value of b1 must keep changing for every click. someting like this A1 B1 1 d 2 f 3 h 4 a etc
View Replies!
View Related
Creat Random List
I need to create a file with random purchase list that have some restrictions. I’ve been working on this file for few days, but I couldn’t get desirable results. Attached a sample file that describes what I did. It build up as the final file (just the final file has many more items). I’ll try to explain what I need and how it works: There are 4 categories: Theater, Salon, Kitchen & garden. I need to create random lists of purchases (each list should be a line at the total sheet) there is budget limitation of $100000. Each category has minimum items that must be purchase (headlines are in red color, total of 9 items). The minimum items that can be on one list are 11 (9 red columns & another 2 random columns). Maximum are 15 items (9 + 6). Same item shouldn’t appear twice at the same list. This file chooses randomly 19 items and check if the list stands up the budget. There are 3 issues that I couldn’t solve by myself: 1.I should define the file to stay within minimum & maximum items per list (11-15) 2.Make sure it doesn’t go over the budget 3.Stabilize the list the values will not change each time I’m doing changes to file
View Replies!
View Related
Random List Creation
This might be a little complex to understand but I am going to try to make it clear as to what I am in need of. Basically I have a larger macro that runs and I need to add a pop up that asks for a percentage to be put into a new sheet. The percent is the percent of random rows on the current sheet to be put into a new sheet. Then it would have to create the new sheet with the random information. This is for a audit and we want to have it done randomly and no duplicates. There are no duplicates in the list at this point. The width if needed is from column A to column E.
View Replies!
View Related
Generating Random Number List
I have a spreadsheet of 1000+ companies and I want to take a random sampling of 100 of them. I would assume this could be accomplished by auto-filling a numerical value to each line of data (which I did), then scrambling only the column with the numbers (which I can't figure out), and then selecting the entire text and resorting it to put the numbers back in order and then just work with the first 100 rows (which I can do, if I knew how to make the numbers random).
View Replies!
View Related
Add Amount In Column
Trying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march. The format for the date is 3-12-09. Here is what I'm trying. =SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)
View Replies!
View Related
Generate Column Headings From Part Of A String
In column B & C, starting at row 2, there are first names & surnames. In column H there is a grade for each name, eg B. Columns AC through AH hold subject codes for each name, eg 18E/Hs1 (potentially all columns could contain a code or only 1). The identifying part of the subject code is the first 2 letters after the / . So in the example the subject code 18E/Hs1 the 'Hs' signifies it is History. A table of subject code and their subject name is contained in a named range called Subject_ID (see below). I intend to make this range dynamic. Subject .Subject Code......Name HsHistory EnEnglish ChChemistry ITIT PhPhilosophy MpMaths (Pure) PtPart-time PyPhysics SoSociology BsBusiness Studies SSSports Science FrFrench BiBuilding. For each name (starting at row 2) I want to achieve the following: Scan across the range containing the subject codes (AC:AH), identify the first two letters after the / and match it to the subject name in the list. Paste the subject name to a cell starting at AI1 and then insert the grade (contained in column H) for that student in the corresponding subject column. The next unique subject name should then miss a column be pasted in AK1. Example ......B ......C........H.. AC..........AD...........AE..........AF........AG.........AH......... Matt........Pia......D....18A/Hs1...18B/En1....18B/Hs3 should result in the word History in AI1 and English in AK1 and the letter D in AI2 & AK2. Note as there is already a reference to History this is not repeated again. I hope this is clear. I have enclosed a sample workbook with expected output and colour coded the subject names so that the order that they are pasted in is evident.
View Replies!
View Related
Formula To Return Random Number From A List And Associated Name
My objective is to take two different random counts of numbers written in column b, ranging from B9:B66. However I want to highlight the specific fields listed in the example below, and pull random numbers from those selected fields only. Then I want to pull another set of random numbers in the same column B9:B66, however using a different specific set of fields within column B; as shown in the second random formula string. Use the Press 9 feature to randomly select the two different numbers. What this attempt is trying accommplish is selecting one boy, one girl who have assigned numbers adjacent to their names(name field populated in column c). What am I doing in correctly? HTML Formula written to B70 =INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52)),1) Formula written to B72 =INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B10,B11,B12,B14,B18,B19,B22,B24,B26,B27,B28,B29,B30,B32,B36,B40,B43,B46,B48,B49,B53,B54,B55,B56)),1)
View Replies!
View Related
|
TRACKER
