Assign Unique Number To A String

Jun 4, 2008

I'm currrently using column I and col J

in col I are all the units of measurement: KG, PKT, BAG
in ColJ is the formula to return:

KG as 9999999
PKT as 8888888
BAg as 7777777

if(i2=kg, 999999999,"false!")

i was thinking of using if....but triple if formulas would be long isn't it?

PART2 of this would be using:

to check 5 values and if it is 999999999 it'll return KG
8888888 PKT
7777777 BAg

View 9 Replies


Assign Specific Number To Cells Based On Another Cell Value In String

Feb 27, 2013

So I have this list (I made it a little bit shorter).

So what you see is two different tasks (01 and 02) and three different conditions (A, B and C). In column B you see the result I would like to have. '/Searchtask_01.html' in A1 belongs to conditions A, because it is in session A. However, '/Searchtask_01.html' in A10 belongs to conditions B, because it is in session B.

How to get the results in B with a formula?

View 1 Replies View Related

Assign Unique ID IF Adjacent Cell Has Unique Value?

Jun 5, 2014

I'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.

View 1 Replies View Related

Auto Assign A Unique ID (primary Key)

Oct 30, 2008

I know it is possible in Access auto assign a unique ID (primary key?) to each record in a table. I am creating records in a table using Excel and Visual Basic to write to a ".mdb" file without using Access. Is there a way to auto assign a unique ID within the coding?

The reason I ask this is if I had two users writing to the ".mdb" file at the same time, I don't want them to end up with the same ID's for two differend records.

Sub UploadRecord()
Dim DBName, DBLocation, FilePath As String
Dim DBConnection As ADODB.Connection
Dim DBRecordSet As ADODB.Recordset
Dim XLRow As Long, XLColumn As Long
Dim DataRange As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Worksheets("Find Record").Range("A2").Value = Worksheets("Index").Range("H3").Value
Worksheets("Find Record").Range("B2").Value = Worksheets("Index").Range("I3").Value
Worksheets("Find Record").Range("C2").Value = Worksheets("Index").Range("J3").Value
Worksheets("Find Record").Range("D2").Value = Worksheets("Index").Range("K3").Value
Worksheets("Find Record").Range("E2").Value = Worksheets("Index").Range("L3").Value
Worksheets("Find Record").Range("F2").Value = Worksheets("Index").Range("M3").Value
Worksheets("Find Record").Range("G2").Value = Worksheets("Index").Range("N3").Value

Worksheets("Find Record").Activate.........................

View 9 Replies View Related

Assign Value To Character Or String?

May 30, 2014

I want to assign a value to a character or string

Like I have formula p2p3= (n-1)*p +(2*e)

I want to assign values(number) to n,p, that i can get want to assign 'n'=4

I don't want to use cell number for calling values of n,p,e

View 1 Replies View Related

Assign String As Cell Hyperlink?

Nov 24, 2011

I can't work out how to strip the hyperlink address in cell rRecordID(1,11).

Private Function GetRecord(lRow As Long)
Dim i As Long
Dim vMyCopy As Variant


View 3 Replies View Related

Can't Assign String Value To Designated Cell

Jun 30, 2013

What am I missing here. The following code fragment

strTestString = In_Wrksht.Range("A" & i)
strNameString = Trim(Mid(strTestString, 6))
Debug.Print strTestString & " uuu"
Debug.Print strNameString & " eee"
In_Wrksht.Range("B" & i) = strNameString & " XXX"
Debug.Print Trim(In_Wrksht.Range("B" & i)) & "ggg"

returns these results:

strNameString is not being assigned to B&i. Why Not?

View 9 Replies View Related

Lots Of Text Data, Need To Assign Unique Numeric Values

Aug 28, 2009

Here's my situation.

I will post a rudimentary example:
Account Name/Column A
Truman school, San Diego
Truman school, San Diego
Truman school, San francisco
Truman school, Atlanta
Truman school, Atlanta

In column B, I would like it to assign a unique numeric id based on the TEXT only.

So Truman school, San Diego gets a string of numbers: 1234
Truman school, san fran: 1235
Truman school, Atlanta: 1236

My spreadsheet is about 6,000 records with a great deal of the above situation going on. So school names are identical with different locations assigned. I am trying to import to a database and don't want a lot of duplicate records. That's why I'm trying to assign a numeric value.

View 4 Replies View Related

Macro To Create Unique ID From Email Address And Assign To Repeated Entries

Feb 10, 2014

I am creating a rollup file from hundreds of excel files each has the same structure

Each file will have a dozens to hundreds of names and some files will share entries i.e Juan Alvarez might be in 10 different files

I need to create a unique numerical identifier starting at 0 for everyone based off there unique email address for each individual in the rollup file

So Juan Alvarez would need to be given the same numerical ID every time he showed up in the rollup

[Code] .....

Rollup. After filtering on "Name".

[Code] .....

View 2 Replies View Related

VBA Slow To Assign A Null String To A Cell - On One Computer Only

Feb 11, 2009

I have a VBA program which is running very slowly on one of my computers, but none of the others.

Here's a piece of code which illustrates the problem, which is that assigning a null string to a cell is very slow.

Option Explicit
Public Sub test()
Dim objSh As Worksheet
Dim I As Integer

Set objSh = Worksheets("Sheet1")
MsgBox "test1"
For I = 1 To 1000
objSh.Cells(1, 1) = ""
MsgBox "test2"
For I = 1 To 1000
objSh.Cells(1, 1) = " "
MsgBox "done"
End Sub

If I change this to assign a space instead it is very fast (about 1,000 times faster).

This slow machine is not normally slow; an ACER Aspire 9410Z with 2GB RAM, running Vista Ultimate.

Other machines run this code OK on a mix of Windows XP, 2000 and Vista Business. with Excel 2003, 2000 and 2007.

Ultimate seems to be the only unique factor of the machine where it runs slowly.

View 9 Replies View Related

Calculate Number Of Days Between 2 Dates And Then Assign Number Based On Answer

Mar 22, 2014

How to create a spreadsheet with what I think will be a very simple formula?

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.


Another, maybe simpler, way of saying it is:

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

View 9 Replies View Related

VBA Macro To Assign HTML Content Of Wikipedia Search To String Variable

Feb 16, 2014

I am doing a Regular Expression search on a string variable assigned to the HTML content of a Wikipedia search. However I am currently manually going to Wikipedia, searching for the term, saving the html page, opening the saved page with Notepad and then copying the content into a cell.

Can the above process be automated with VBA, how to assign the html content of a Wikipedia search to a string variable.

View 1 Replies View Related

Assign Number To Row ....

May 1, 2009

I have a list of numbers such as:

B01... the goes on too approximatly D128

and i wanted to know if there is a way to assign the value to the row?
For example:
A23 in column A row 23
A53 in column A row 53

is there any method that achieves this?

View 10 Replies View Related

Assign Number To Text

Jan 23, 2014

I currently have a spreadsheet which I use to do an audit of the office stationery supplies.

There 3 columns at the focus of this question:

F - Number of items in stock
H - Stock re-ordering level
I - Order Required

I currently have an IF statement to tell me if I need to re-order any stock, by comparing the Number of items in stock against Stock Re-ordering level.

The statement is =IF(H1>=F1, "Yes","No")

This works fine under most circumstances. However, in some of the H cells, I have the text "Special" which indicates that the stock will only need re-ordering on a special occasion.

Any value I enter in F will cause the I to say "Yes".

Is there any way to make the word "Special" equivalent to 0, so I says "No"?

View 4 Replies View Related

How To Assign Number To Course Name In Input Box

Jun 26, 2014

In column 'o' I have inserted a number for course name in column 'p'. I want to assign a course name to that particular number as follows:

Column 'o' Number ... Column 'p' Course name
1: A. B. C.
2: J. K. L.
3: P. Q. R.
4: X. Y. Z.

And so on as per course names.

If I enter number 1 in inputbox then I want to enter the course name in column 'b4' respectively.

View 2 Replies View Related

Assign Week Number

Jul 12, 2006

I have a list of random dates between 09/01/05 and 07/01/06. I would like to assign a week number to each date. For example, I would like it to be week 1 if the date is between the dates of 09/01/05 and 09/07/05. Is there any simple way of going about this?

View 3 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

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

View 7 Replies View Related

Change Current Cell Value If Number String NOT Letter String?

Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies View Related

IF Statement - Assign A Number Value Of 1 To All The Records?

Aug 6, 2014

I have a medication start end and admission date.

and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.

View 3 Replies View Related

Find Cells With Same Value And Assign A Number To Each

Jun 11, 2009

I use my spreadsheet to keep score during tournaments. when i calculate my leaderboard at the end of the event i list the results first place all the way down until it finishes the list of players. i need to be able find players with the same or equal score and list them as tied for the same place.

another words if i get to the 3rd place and i had 3 players tie for third i need my leaderboard to show them all in 3rd place . currently it would number then 345. i listed below how it should format it. i also attached a file it will make it more clear.

View 2 Replies View Related

How To Assign Week Number Against Dates

Apr 15, 2012

We have started a new venture from 5th April & I need to create a template for a year and assign week no against the dates. The week1 will start from 5th April & week 52 will end on 31st March.

Also there will be a summary of the Week No(Starting Date & End Date) against each week at the top of the template. I could put the formula for the summary part but I am unable to figure out how to assign week no against the dates as given below. It is very difficult to assign the week no manually for the whole year & also it is vulnerable to error. a formula across B7:B23 which can deliver the desired result?

Sheet2  ABC1Week NoStart DateEnd Date2Week15-Apr8-Apr3Week29-Apr15-Apr4Week316-Apr22-Apr5   6DateDesired
Result 75-AprWeek1 86-AprWeek1 97-AprWeek1 108-AprWeek1 119-AprWeek2 1210-AprWeek2 1311-AprWeek2 1412-
AprWeek2 1513-AprWeek2 1614-AprWeek2 1715-AprWeek2 1816-AprWeek3 1917-AprWeek3 2018-AprWeek3 2119-

[Code] .........

View 9 Replies View Related

Assign A Number Value To Text Within Worksheet

Sep 10, 2009

I need to count the number of times a value appears in a row and assign a number that tells which occurence of the text it is.
For example

Name Address
Jim 123 Smith Street
Jane 123 Smith Street
Bob 543 Apple Street
Mary 543 Apple Street

I would like a way to insert a column that puts a 1 next to Jim and a 2 next to Jane, a 1 next to Bob and a 2 next to Mary and so on.
Ultimately, I want to sort the list and delete all the twos, thus deleting the duplicate address entry.

View 9 Replies View Related

Conditional String: Repeat The Same Account Number In The String

Jan 10, 2009

I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?

View 4 Replies View Related

VBA To Count Occurrences On A List And Assign Number

Jun 6, 2013

I have found ways to count cells but what I am trying to do is in column F I have a list of meeting topics, and sometimes these repeat in a year. in my drop down menu I have all of them listed however my supervisor wants me to add a count after the meeting number in the 1_1X format where x is the number of times a topic has been used.

The output will be added to my macro here


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim y As String
Dim z As String
Dim b as Integer

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("MEETINGNUMBER")) Is Nothing Then Exit Sub

[Code] .......

View 3 Replies View Related

Using RAND Function - Assign Number From 1-5 To Each Of Identity

Apr 29, 2014

I have 379 sample identities in my Excel spreadsheet in A2:A380. I want to randomly assign a number from 1-5 to each of these.

View 1 Replies View Related

IF THEN Statements: Assign A Letter Grade To A Number

May 9, 2006

I have figured out how to assign a letter grade to a number, but am having trouble assigning it the other way, a number to a letter grade. For instance: If a student gets an A, I want the column next to it to indicate that the A represents a 4; a B represents a 3; a C represents a 2; D a 1; and F a 0. This will allow an easy grade point average calculation.

A 4 History
C 2 Math
A 4 English
B 3 Physical Ed
D 1 Science

GPA 2.80

View 3 Replies View Related

Assign Duplicated List Entries Same Number

Mar 4, 2008

I have a long list of items. I would like to assign the same number to each of the replicates. I've been trying to expand on this: = COUNTIF($A$1:A1,A1). which increments the number for each replicate, but don't seem to be getting anywhere. The attached file might make it clearer what I'm after.

View 3 Replies View Related

Vba To Have Command Button Click Assign Number To Another Userform

Dec 23, 2013

Userform2 is popped up from another userform3.

How can I have userform 3 when clicked fill in a textbox on userform2 and then finally assign it to cell F3 on my worksheet.

Private Sub CommandButton2_Click()End Sub

So I need commandbutton2 (which needs to retain a value of 1` when clicked ) on Userform3 to assign the value of 1 to TextBox44 (Which is using control Source F3 so the value here ends up in F3

Private Sub TextBox44_Change()
If TextBox44 > 5 Then
MsgBox "Your entry must be part between 1 and 5", vbCritical
Exit Sub
End If
End Sub

View 2 Replies View Related

Assign To A Variable, The Total Number Of Text Entries In Col

Feb 28, 2009

I need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).

View 5 Replies View Related

Code To Assign Predefined Number In Separate Worksheet

Apr 27, 2009

Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.

I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.

I've attached a sample workbook.

If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.

View 7 Replies View Related

Copyrights 2005-15, All rights reserved