Formula Required To Take First Letter In 1 Cells And Add Random Number To Create Unique ID
Mar 27, 2014
I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
View 10 Replies
ADVERTISEMENT
Feb 5, 2008
I'm needing to generate a unique random value for a database with 3546 cases. The unique random values cannot be duplicates of each other. I tried the =RANDLOTTO function that I learned of in an old post on this board, but that results in "#NAME?" appearing in the first cell. I tried to install the Add-Ins (both the Analysis ToolPak and the Analysis ToolPak - VBA, but nothing seems to happen. Is there another way to generate these numbers?
View 9 Replies
View Related
Feb 19, 2008
I have 12 Cells, years 1 to 12. I want to create a random number for each cell, which then depending on what the number is, either 1-75 being 8,000,000 or 76-100 being 10,500,000, place it in the cell and be able copy it down. What i have done already you can see on the attached workbook, or the table i used:
I used this table to generate the random number to give either 8m or 10.5m, except i put the VLOOKUP in the year 1 cell, added some dollars signs and copied down but that only makes them all the same, so i want a way to make each year have its own random number preferably without making 10 tables.
View 3 Replies
View Related
Jun 7, 2009
I just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.
for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...
(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)
View 9 Replies
View Related
Mar 31, 2014
Is it possible to add letters to this "Unique random number" generator it is very fast and takes only 5s to run on 50,000 rows, I have a different "Unique random Sequence generator" macro but it takes about 30 minutes to run on 50,000 rows
Code:
Sub generateuniquerandom()
Dim b() As Boolean, e As Range, k&, x&
Dim lRow As Long
With Sheets(1)
lRow = .Range("F" & .Rows.Count).End(xlUp).Row
[Code]...
View 3 Replies
View Related
May 5, 2009
I have a formula to generate a random number between 0 and 10.
View 10 Replies
View Related
Jul 18, 2012
How do I create a 6 digit unique random number for use as an ID in column A. Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added.
View 9 Replies
View Related
Jul 30, 2006
For my assignment, I am supposed to create a worksheet that will generate a random social security number (using the correct social security form of 000-00-0000) There are supposed to be a billion social security possibilities and each should be equally likely to be selected in the worksheet.
View 20 Replies
View Related
Mar 26, 2008
I have a table with 36 numbers - 6 x 6 that means 6 rows and 6 columns. Numbers 1 – 45.
I want Excel to shuffle the numbers (random shuffle) so they will come in different combinations on each row and column BUT the numbers must not repeat (no duplicates) in the same row or column.
View 9 Replies
View Related
Apr 1, 2009
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.
A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)
Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)
Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.
View 10 Replies
View Related
Sep 24, 2012
i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.
View 9 Replies
View Related
Jul 11, 2009
I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
View 14 Replies
View Related
Jun 7, 2009
Is there any way to name cells with same letter but different number?
e.g i need to name the first row A1 to A100.
View 8 Replies
View Related
Oct 4, 2012
I am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)
In the A column, it looks like this:
CV42 6AQ
FC45 D4D
West yorkshire
PR42 6RD
Etc.
i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.
View 3 Replies
View Related
Mar 20, 2007
I'm making a user form in excel/vbe, that will enter data into a worksheet behind the form. I would like to make a unique reference number that is automatically generated by the form as the next in the series. It can be as simple a number as 00001 so nothing too complicated I hope.
When the user opens the user-form i would like the reference to appear on the form so that they know what it is. Then when they enter the data I want the number to go in the database with all the rest of the info they have entered and also for a message box to come up reminding them to rememeber their number.
View 9 Replies
View Related
Nov 25, 2009
I know how to use RAND for numbers, but is it possible to use RAND for letters?
View 9 Replies
View Related
Dec 23, 2009
Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?
View 3 Replies
View Related
Jan 5, 2014
I have the following formulae to pick up a random number from 0 to 9999.
=TEXT(RANDBETWEEN(0,9999),"0000")
I want to start this random search from a specified number. for example if i press F9 it should show 3456 at first press and then onward.
View 1 Replies
View Related
Apr 2, 2007
i have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?
View 9 Replies
View Related
Jan 27, 2009
I have 4 names, and a grid of 100 boxes. I need those 4 names randomly deposited in the 100 boxes, making sure each name is displayed an equal number of times (25 each) Is there an Excel formula to compute this?
View 9 Replies
View Related
Jun 15, 2014
i need a formula for a sign in sheet for the academy i work at. we have night and day classes of the same sessions, in the planner they are listed as d1, d2 d3 and n1 n2 n3 and so on for each.
so basically im trying to create a formula to quickly ender the d1, d2, d3 ect into the cells on eachpage for the students to sign in for each day it works out to be about 190 calls and 9 diferent classes to you can see why id like to speed this up. problem is i need the number to change not the D and excel want to use the cell allocation.
as you can see from the document i have not attatched, i have created a formula to add in the dates automatically, i would like to do the same with the session numbers or the D numbers, this is one of the shorter courses and has about 190 days
View 4 Replies
View Related
Dec 26, 2011
I have following table,
Item NoSectorDestination
12346589BOM-DXBN/A
12346589DXB-FRAFRA
12346589FRA-DXBN/A
12346589DXB-BOMN/A
87665976NAG-BOMN/A
87665976BOM-DXBN/A
87665976DXB-PARPAR
87665976PAR-DXBN/A
87665976DXB-BOMN/A
87665976BOM-NAGN/A
Row 2 to 5 are of same item no. The result that is required is in Column C. The formula to check same item numbers in column A and give the result as "FRA" in column C3 with C2, C4, C5 showing as not applicable. Similarly for the next item no
View 3 Replies
View Related
Dec 19, 2013
I am creating a "board game" type game in excel and one of the functions will be landing on a cell. When you are on this cell you click the checkbox to the left of it which then flips the value to true. I then have another formula generating a random number between 1 and 100 if the value is true. I would like to make cards in a separate sheet out of single cells but make about 40-50 of them maybe less. I want it to be able to pull a card(cell's) contents into another cell on another sheet. But I want it to be a random card associated with the random number generator. I can definitely make changes if this isn't possible but the point of it is to show the contents of one of the random cards.
View 2 Replies
View Related
Feb 12, 2009
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 9 Replies
View Related
Nov 25, 2008
i need to create a random 3 letter prefix form eg. British Head Office, how would you pick letters out and compare the 3 letter prefix back to a column with existing prefix. and if u can help me even more how can i automate this method beacuase i for eg. get like 50 company names at a time and dont have time to do and check them 1 by 1 lol.
View 9 Replies
View Related
Dec 30, 2009
i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.
View 9 Replies
View Related
Apr 6, 2009
I am copying various rows from one spreadsheet to another (sheet3) and would like to total one of the columns. The trouble is that since I don't know how many rows there will be I am having trouble inserting a formula that will work. I am sure that there must be a simple solution but I can't seem to find it.
View 2 Replies
View Related
Jun 15, 2009
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies
View Related
Feb 12, 2009
I have a column that looks something like (it is VERY long and has over 20 different names):
abe
abe
abe
abe
ben
ben
ben
cat
cat
john
john
john
john
tex
tex
I want to create another column (automatically) that lists each of of those once:
abe
ben
cat
john
tex
Any idea of how I can do this automatically?
View 12 Replies
View Related
Nov 17, 2008
Format Random Number Cells As Draw Result Entered ...
View 9 Replies
View Related
