Unique No Generation
Apr 5, 2007my requirement is attached here with.
in generating the unique no?
ADVERTISEMENT
Number Generation
Apr 26, 2007I have multiple users that use a spreadsheet to enter data. I am trying to figure a way to assign a case number to each row of data that is entered. I cannot really do sequential numbers, because often people are entering data at the same time, and often these people are working offline.
So I was thinking if I could somehow have the case number be a few letters (to identify a specific person) and then a series of numbers (to signify which case number it is). That would allow multiple users to enter data offline simultaneously without having to worry that the case numbers are going to be the same. Maybe I could use the Application.UserName and somehow parse it to give me the letters (perhaps Intials or something), and then somehow apply sequential numbers to that...
for instance, JJH00001, JJH00002, etc
Automated Ref Number Generation
Oct 7, 2009I m trying to achieve is generating ref numbers automatically.
I have attached an example.
In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening
Automate Report Generation
Jun 17, 2009I have multiple files that I need to get data from. My dream is to push a button and gather the data from variable files. (I know how to make the button and get data from single file).
The file name is below:
Cell A1 contains this formula ='C:ReportsYear 2009[CityWaterReports Jan 2009.xls]PHFlows'!$B$44
Cell A2 contains this formula ='C:ReportsYear 2009[CityWaterReports Feb 2009.xls]PHFlows'!$B$44
and so on.
I would like the customer to be able to push the button and be asked to enter the year(eg: 2008) and have the formula update to the new year in each cell.
Automatic Sheet Generation
Nov 3, 2008I have only two sheets..first sheet is the user sheet where the user gives the data...second is the sheet where in we get the result according to user given data..
there is a cell in User sheet where User gives a Number. for example " 8 "
so sheet2 : here we have only 9 rows. as user gave "8" the fisrt 8 cells in the first column should see like this
B 01/08 , B02/08 , B03/08 as so on till B08/08
when user gives 9 in user sheet
then the first sheet is finished with all the 9 rows ending with B09/08
when user gives 10...then sheet 3 should automatically appear with the first row and first colum saying B10/08.
when user gives 20 in user sheet...then sheet4 should appear with last number as B20/08
so each sheet is having only 9 rows..
first sheet ends with B09/08
second sheet ends with B18/08
third sheet ends with B27/08
MC W/Random Number Generation
May 4, 2006I get the following error whenever I try running a Monte Carlo simulation with iterations in excess of 64000 (The MC simulation calls the 'NormSInv' worksheet function at least once per iteration, and the argument to the 'NormSInv' function is always a standard normal RANDOM number also generated by Excel VBA): Run-time error '1004': Unable to get the NormSInv property of the WorksheetFunction class
Why do I inevitably encounter this error ONLY when the number of iterations exceeds 64000, for instance? Simulations with iterations < 64000 run smoothly without a glitch.
Advanced Random Number Generation
Jul 29, 2009I understand how RAND and RANDBETWEEN work, but have a slightly more complicated random number generation problem that requires a number that isn't solely between two numbers.
I have one cell that is randomly generating a number between 0 and 350 (we'll call this value A) and another randomly generating a number between 0 and 15 (value B). Then, I have a third cell (value C) that equals 15 minus B. Where I am having trouble is with a fourth cell (value D). This cell I want to have randomly generate a number between 0 and 350 but it needs to be D less than the value in A or C more than the value in A. Is there an advanced random number generation technique that can solve this?
List Generation Across Multiple Sheets
Aug 10, 2014I am currently working on a price configurator which has more than one sheet with parts and prices on it.
what i want to do is have a sheet where i can populate a parts list from the parts that i have values for.
i want it to only pull the lines out of the previous sheets with values next to them.
Generation Of Random Numbers And Sum Of Those With A Condition
Oct 4, 2005I have three columns A,B&C In the column A Iwill generate random
numbers between 40&50, In the Column B I will generate random numbers
between 35&45, The column C is the sum of Column A&B(i.e. C=A+B) but I
the sum should be between 83&88. I need acondition such that the random
numbers generated in Column A&B Should satisfy the Column C(A+B)
condition sdatisfying the A&B columns condition.
Automatic Generation Of A Report Sheet
Jun 14, 2007What I have got is a list of exams and subjects on the first sheet from cells A3 to A whatever. This list varies in length from a few exams to many dozens.
Under each exam is a list of subjects.
i.e.: Under the math exam is addition. multiplication, division etc
On columns B through ZZ and beyond. I have individuals names.
Under those individuals names (opposite the exam row) I have pull down boxes saying if the individual has passed failed or did not sit .. etc.
What I would like to be able to do is to automatically generate a second sheet with the individuals name on it with a report of what exams they took with what result. Of course it would be nice it was nicely formatted with the headings of all the different exams and the subjects taken arranged in order.
If an exam was not taken it should not appear in the generated sheet
I have a fairly good knowledge of Visual Basic though I have not had much to do with VB for scripting.
Followup On Automatic Report Generation
Jun 24, 2009The following code works very well up to a point. See previous thread Automatic Report Generation.
As I use it to change my yearly summary sheet all at once. However as it stands I need to insert a seperate line of code for each month and cell that I need data from I will have over 2000 seperate lines when I am done. Also if the file does not exsist yet then the cell returns a #REF (error 2023). And none of the other formulas in spread sheet will work. Is there a way to have the cell return a zero if the file has not been created yet?
Is there a way to try and automate more of the code below? Cell references will be all over the place. All the months for the year will be needed.
Random Numbers Generation In Column
Oct 24, 2012The code below generates numbers in the range (A2: H2)
View 4 Replies View RelatedRandom Numbers Generation, No Repeats
May 8, 2006I'm trying to write a code that will generate random numbers and no number appears more than onece.
For I = 0 To 3
For J = 0 To I
Do
n = Int(4 * Rnd) + 1 'random number generated
Array1(I) = n 'random number settled in an array
If (I = I - J) Then 'checking for the same place in array
numsOK = True
Else
If Array1(I) = Array1(I - J) Then ' comparing two different places in an array
numsOK = False
Else
numsOK = True
End If
End If
Loop Until numsOK = True
Next J
Next I
Automatic Report Generation From Excel Worksheet
Apr 24, 2014want to generate automatic report from excel work sheet
View 1 Replies View RelatedRandom Number Generation Without Repeating Across Same Row Or Column?
Nov 24, 2011Been trying to think of a way to get random numbers 1 to 36 generated without repeating in the same column, however also getting it to perform the same opertaion in 9 more columns (B:J) without the same number appearing in the same row.
example
1 2 5 4
2 5 3 1
3 4 1 5
4 1 2 3
5 3 4 2
Unsure if this is possible, have created a basic script for random number generation but have no clue how to expand across columns.
Graphs / Charts Generation Automatically In Excel?
Sep 5, 2012I want to prepare a graph / chart for the column A and column B. Column A has headers and B has the data that I want to represent in the graphs. The real problem in this is that the two columns length is not fixed. For eg. for January Column A & B could have 5 rows of data but for February they could have 10 rows of data. I dont want to prepare the chart manually every month. I want that if we could apply any VBA coomands that could prepare the chart automatically irrespective of the rows count.
View 9 Replies View RelatedSmart Generation Of Coordinates For Excel Shapes VBA
Sep 5, 2013I'm using Excel to create a flowchart from a configuration sheet. I'm not manually entering the coordinates. Instead, I draw the start step in a set location. The next step(s) that's connected to the first step is placed directly below the start step. if there's more than one step connected to the start step, it's placed at the same y-coordinate but shifted along the x-axis.
The problem I'm running into here is this: say I have three steps connected to the start step and three further steps connected to the first of the three connected to the start step, how can I keep track of this and shift the other two steps connected to the start step along the x-axis so they're not positioned above the four steps below the first step (and as such, in the same location as other steps)? I'm not sure if my description is particularly clear or not so I've attached a drawing that will hopefully clear things up.
Is there any way of giving Excel shapes a "weight" so that other shapes won't line up on top of them?
Interesting Question - Random Number Generation
Oct 1, 2008it is possible to generate a completely random number generation in excel?
Other than using the rand() function and then sorting by top or bottom (as then have affected the sample by choosing which direction it is sorted).
And the randbetween() function changes everytime you modify a cell.
Random Value Generation From Skewed Normal Distribution
Sep 20, 2008I would like to generate a random value using a skewed normal distribution. For inputs I have the sample mean, standard deviation, and skewness. I would like to find a way to generate numbers randomly that would fit the skewed normal distribution described by the inputs.
Some information about skewed normal distributions can be found here: ...
Pass Random Generation In Cell To Variable
Jan 1, 2008I want to generate a random number based in a poisson and i use this
Application.Run "ATPVBAEN.XLA!Random", ActiveSheet. Range("$B$2"), 1, 1, 5 , , 35
But i want to save the number in a dim single variable called N. I try to put N where i have write ActiveSheet.Range("$B$2") and do N=Application.Run ...
But it doesn't work.
Auto Generation Of String Based On User Input
Jun 3, 2009Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
Email Generation Using Macro - Add Certain Data From Spreadsheet Within Body
May 18, 2014Trying to add a macro to generate an approval email with certain data from the spreadsheet within the body of the email. I have the following in place currently to deploy the email:
Sub Sendemail()
Dim Email_Subject, Email_Send_To, _
Email_Cc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "New Account Approval"
[Code] ....
What do I need to add the following cell values into the body of the email, All Sheet 1?
B6
B4
B8
F14
E16 : F16
E17 : F17
A25 : B25
A26 : B26
A27 : B27
Is there something simple I can insert to make this work?
Variable Rows Per Column/ Automatic Combination Generation
Jun 19, 2007The following table/ code is something which I've been trying to tailor from a previous post so I'm not taking the credit for what I think is some very good code. Unfortunately I can't find the link to it - sorry!
Right, I have a number of columns containing a various amount of data entries in each with the first row being the header. I would like to generate all possible combinations of this data in one column, the entries separated by commas, that will eventually be exported as a csv file.
The number of columns and number of rows in each column will be changed regularly ...
Dynamic Subroutine Generation Creates Compile Error
Oct 4, 2007I'm am running Excel 2003 SP2 on Windows XP SP2.
I have a macro which modifies cells in a spreadsheet. But for brevity sake, I have an empty spreadsheet which has an Auto_open macro and two subroutines in it. The subroutines initially consist of just the Sub and End Sub statements.
The Auto_open procedure reads in lines from two text files (generated by another application), and inserts the lines into each of the empty subroutines. Auto_open then runs the two subroutines.
I have been able to get this dynamic creation of the subroutines to work for one subroutine, but not for the second. I receive the compile error: "Only comments may appear after End Sub, End Function, or End Property". I don't see anything wrong with the inserted code.
The modified subroutines appear fine, and if I save the macros with the modified code, close excel, rename the text files so they are no longer read in, and re-open the spreadsheet, the auto_open procedure and the two subroutines run fine. This tells me the code itself is okay, and yet it won't work during the initial run.
Below is the macro code in its original state:
Sub Auto_open()
Dim fso, f
Dim VBCodeMod As Object
Dim LineNum As Long
Dim StrFileName As String
ShowVisualBasicEditor = True
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
End Sub
----------------------
The contents of the read in file, %TEMP%subA.txt, is just one line:
MsgBox("inside SubA")
The contents of the read in file, %TEMP%subB.txt, is just one line:
MsgBox("inside SubB")
------------------------
When I open the spreadsheet the first time, it gives me the first message box from Sub A, but then generates the error and highlights the Sub SubB() line.
To duplicate the problem:
1. Insert the macro into a spreadsheet. Save and exit it.
2. Create the files %TEMP%subA.txt and %TEMP%subB.txt containing the single MsgBox lines.
3. Open up the spreadsheet. SubA will run and a message box will appear. Then the compilation error will occur.
4. Save and exit the spreadsheet.
SubA will now contain:
Sub SubA()
MsgBox("inside SubA")
End Sub
SubB will now contain:
Sub SubB()
MsgBox("inside SubA")
End Sub
5. Rename the two text files, so that the next time you open the spreadsheet it won't try to insert the lines from the files into the subroutines.
6. Re-open the spreadsheet. Two message boxes will now appear, one from SubA and one from SubB.
Generate A 6 Digits Code Based On Filename And Time Of Generation?
Aug 20, 2009I need to enter a revision string into a file.
Basically, I need to combine the filename and its generation time and then encode it into a string (i.e. a 6 digits HEX code or 6 digits using [0-9][a-z][A-Z]) so it would generate a pretty much unique revision number.
So, is there any function that you can think of that would do something similar?
If not, what is the best to tackle this?
Generate a CRC or md5sum?
What would be in your opinion a quick and efficient way to solve this tricky one?
Lottery Number Generation: Create A Single Line Function
Mar 29, 2002You must install "Analysis TookPak-VBA" before doing this; check in your Excel's Tools/Add-Ins menu. Paste this long function (see below) in an Excel's worksheet (any cell) and press F9 (calculate) until you see at least 2 identical numbers. Real life's lottery does not have repeating numbers. I can disable this repetition by using cells relation but I want to use just one (1) cell. Any expert wants to challenge? If not, you can use VBA. But it still requires one (1) line of codes.
=RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)
Macro For Name Generation From Product To Product With Sizes And Filenames
Jan 30, 2014I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
View 4 Replies View RelatedCount All Unique Values (text) + Definition Of UNIQUE :D
Aug 24, 2009If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies View RelatedAssign Unique ID IF Adjacent Cell Has Unique Value?
Jun 5, 2014I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:
0001: Apple
0001: Apple
0002: Google
0003: Sony
0003: Sony
0003: Sony
0003: Sony
0004: Ford
0004: Ford
0004: Ford
.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.
# Of Unique Dates Per Unique List Entry
Oct 22, 2009Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
