VBA - Generating Random Characters In Cell With Format Of Yymmdd / Randcharacters / Numbers
Mar 29, 2014
I would like to ask if there's a way to generate a random 4 characters in one cell and the specific random 4 characters that has been generated will be copied until the last cell of my preferred choice?
The format that I want is that:
yymmdd / random 4 characters that has been generated / 4 numbers that will increment sequentially
in A1: 140330QWER0001
in A2: 140330QWER0002
in A3: 140330QWER0003
But here's the catch, the file that I am using when closed then opened again will generate another random 4 characters with the same format. If i opened this file tomorrow:
for example the date for tomorrow is March 31, 2014, the file will do:
in A1: 140331TYUI0001
in A2: 140331TYUI0002
in A3: 140331TYUI0003
I tried to record it, the Rand() function when copied will generate another 4 characters.
How to generate semi-random numbers? I have a small sample (26 points) that I would like to extrapolate out and plot on a skewed bell curve. The numbers represent a ratio which can never be less than one. The average of my sample is 1.0149 and the standard deviation is 0.01234. When I use the random number generator tool in the data analysis pack, it generates mostly good numbers, but it also produces numbers like 0.98 and such that are never possible in my data. I've made a plot with the random numbers, including the sub-one ones, and then just adjusted the x-axis so that they wouldn't show on the graph, but I know that that's not the correct way to do it. My graph isn't skewed as much as it should be.
I'm looking to generate correlated random unifrom numbers between 0 and 1 using the excel random number generator, rand(), and a user defined correlation matrix - without the use of a plugin. I realise there are plenty of risk plugins I could use, but was looking for something excel/vba reliant so I could distribute it without the user having to install anything.
I've tried using a cholesky matrix, but most of time it resulted in one of the numbers being 1, and higher that all the others.
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).
Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2
I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Cell A1 has 123XVF1234 Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
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.
I have what seems to be a pretty complicated situation involving random numbers and letters. I need to generate random sets of this combination of 2 letters follows by 6 numbers (ex. AB123456). I need the final product to be randomly generated and non-repeated.
I have played with various methods of producing random numbers and random letters both repeated and non-repeated. The closest I can get would be a cell of randomly generated letters adjacent to a cell of randomly generated numbers which so far has been useless.
I have the following code which formats the cell values in column A to 10 characters on entry:
Sub FormatToTenCharacters() Dim Cell As Range, LR As Long LR = Range("A" & Rows.Count).End(xlUp).Row For Each Cell In Range("A1:A" & LR) If Len(Cell.Value) < 10 Then Cell.Value = "'" & Application.Rept("0", 10 - Len(Cell.Value)) & UCase(Cell.Value) Next Cell End Sub For example:
Enter excel in A1 and it changes to '00000EXCEL Enter 123456 in A2 and it changes to '0000123456 Enter abc456 in A3 and it changes to '0000ABC456
I want to format it as text so that it does not have the apostrophe (').
I need a formula that I can put into a cell, I assume via data validation, and will prevent the use of any symbols other than a hyphen "-". Spaces are fine. The cell is being used to enter the 1st line of an address.
I'm working on a report right now that I would like to identify incorrect phone numbers. So I'm looking to use the LEN function to identify and highlight any phone number that is incomplete (like missing area code). But I also don't want it to highlight if there is an "N/A" in the cell.
So I was using =LEN(A1:A100)<10 since 10 digits would be a complete phone number). But I don't know how to manipulate it to be if the cell is greater than 3, but less than 10.
The attached file "Temp.xlsm" has been provided to me. I need to generate two files from it - 1) csv 2) a text file in a certain format. It should basically look liked the attached file "InFile.txt" which is an input file for a modelling tool. The arrays and struct strings need to be within brackets and it only contains inputs. Outputs don't need to be generated.
With the code in "Temp.xlsm" so far I have been able to generate attached file "Temp.txt". The only missing bit is the formatting of arrays and structures.
So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).
I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.
So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.
My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs 40m20secs 2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
I use the first six numbers of a SA Identity number to calculate the age of a person as these six digits are actually the year (yy) followed by the month (mm) followed by the day (dd), I am born 01 September 1962, and therefore my first six digits are 620901.
Assume the figure 620901 is placed in Cell A1
Now, in another cell, say A4, I have a future date, but this value is formatted as dd/mm/yyyy. Assume this value to be 01/10/2017.
Firstly, I'd like to know How old I AM at that date and secondly, just because I am battling so, how old I will be on my next birthday, because adding the figure 1 to a total has never been so useless - it just doesn't work! I tried adding all sorts of numbers for months and days in a year but there was no consistency.
So here is my question: Simple - how do I get this to work?
On 1/10/2017 I will be 55, or turning 56 At Next Birthday. I have the following function that gives the answer of 55, but not 56 ANB even after 1 additional year is added to the function (the cell is formatted as yy):
Cell A6 Function = A4-DATE(LEFT(A2,2)+1900,MID(A2,3,2),MID(A2,5,2))
Also, ON my birthdate, 01/09/2017, it says that I am 54, and it has to be wrong because I would have turned 55, unless the function uses time and not just the actual date somewhere. On the next day it does, at least, see me as 55.
how to add calculate what my age will be AT MY NEXT BIRTHDATE for any given date in A4?
Down loading data I receive dates as a number that indicates YYMMDD (ex. 130603 is 03 JUN 13). Is there an easy way to convert these number strings to the actual date indicated other than typing it in?
I have certain cells in column ‘D’ with a ‘nine digit code’ then a ‘space’ and the word ‘sum’ e.g. ‘123456789 sum’.
What I require is the formula to enter in ‘formula is’ when you select Conditional Formatting so the cells in column ‘E’ changes to a green background if the corresponding cell in column ‘D’ has text in it and the last three characters are the word ‘sum’
I have a list numbered 0 to 300. What I am looking for is a formula to randomly select 150 unique sets of two numbers and list them in separate columns for a competition draw and should appear as per the example below
I am looking for a way to create a random order of numbers in a given range. For example, A1:a100 to be populated with a random number between 1 and 100, but not to duplicate, using every possible number within that range. I use the RANDBETWEEN function to generate the numbers, but I cannot prvent them from duplicating.