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
ADVERTISEMENT
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
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
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
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.
OR
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
May 1, 2009
I have a list of numbers such as:
A01
A23
A53
A64
A74
A128
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
Jul 21, 2014
I am using a ComboBox on a worksheet where the items in that box are pulled from Sheet1A:A. When the selection of the item in the comboBox is made, I would like several textboxes on that same page to be auto-populated based upon info derived from cells adjoining whatever row in Column A was chosen on Sheet 1.
For simplicity sake, let's say the range on Sheet 1 is A:H, and TextBox1 = "B", TextBox2= "C", etc.
so if,
A B C D
JOHN 15 DOGS BLUE
SUE 18 CATS RED
BOB 20 TURTLES YELLOW
When Combobox selection is JOHN, I want TextBox1= "15" TextBox2= "DOGS" TextBox3= "BLUE"
View 2 Replies
View Related
Aug 23, 2008
Split from Copy, Cut, Fill Relative Or Absolute Formulas
Originally Posted by AAE
Welcome to Ozgrid.
Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or $A$1*$B$1
Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.
Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.
See this training page on how to toggle between relative/absolute cell references: [url]
Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.
Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.
View 8 Replies
View Related
Dec 8, 2013
I am trying to return the value (date) of a construction schedule by searching for a specific construction activity ID number. Is there a method I can use which incorporates a text search so that as the schedule grows (cell locations shift down) the lookup function still follows the unique activity ID?
Below is a sample of row of the ID I must search for, and the date I must return (on a separate excel file):
A
B
C
D
-
Activity ID
Description
Start Date
End Date
1
L3S4C10020
Supporting Walls to UPTS Slab 3
19-Jan-14
25-Jan-14
View 1 Replies
View Related
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
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
Aug 1, 2006
I am struggling with working with specific cells from specific sheets. e.g.
I want to make a label display teh value of a specific cell.
(i is the row number of the cell i'm interested in)
'now make labellaptime.caption = the cell (i-1, 3) of sheet1(jules)
'LabelLaptime.Caption = i - 1 'works no problem
'LabelLaptime.Caption = Application.Worksheets(Sheet1).Cells(i, 3) 'laptime
'LabelLaptime.Caption = Application.Worksheets(1).Range("i-1,3").Value
'LabelLaptime.Caption = Application.WorksheetFunction.VLookup(
'LabelLaptime.Caption = Range("c3") 'works no problem
'LabelLaptime.Caption = ThisWorkbook.Worksheets.("sheet1").range("c1").value
I've based all this on the book i'm trying to learn from but it doesn't work and (obviously) i don't know why...
(plus any links to info on how to work with specific cells on specific sheets e.g making a variable = to the value of a cell(x,x) of sheet 3)
View 8 Replies
View Related
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
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
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
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:
=IF((SUMPRODUCT(--(db!$A$2:$A$36000=$B$1),--(db!$F$2:$F$36000=C$5),--(db!$G$2:$G$36000=C$6),--(db!$E$2:$E$36000=$A7),--(db!$D$2:$D$36000=$B7),db!$I$2:$I$36000))=9999999,"KG","Invalid")
to check 5 values and if it is 999999999 it'll return KG
8888888 PKT
7777777 BAg
View 9 Replies
View Related
Sep 22, 2005
I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column for
that training event rather then 9.75 for example. But we also want to be able
to add up all training for that individual for the year and see what we paid
them.
The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.
View 9 Replies
View Related
May 29, 2012
I want to assign a text to a value. e.g if i have 2, it should come up as negative.
View 9 Replies
View Related
Apr 5, 2009
I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.
I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.
View 4 Replies
View Related
Aug 6, 2007
Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?
how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.
View 3 Replies
View Related
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
Code:
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
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
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
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
Nov 10, 2008
First challenge will be assigning point values to Words so I can them up and create averages etc
So I have 4 words I need to learn to assign point vales to, then create a formula that will count the points in a row. That will get me off and running,
So here is a very vague idea of what I mean, this would go in A5 as a example
if A1:D1= complete+2 incomplete+0 submitted+1 missing-1
so if I had a row that was:
Submitted | Submitted |Complete| Complete | Missing | 5
( 5 would be the value the formula would return 1+1+2+2-1)
View 4 Replies
View Related
Aug 22, 2012
I have a series of values in one worksheet across one row. The row contains "Yes" or "No" text and also some currency or other number values.
My spreadsheet looks like this (the blank shown is a cell that has no value or text):
Yes No Yes No (blank) Yes No $100 50% 5
What I'd like to do is to have a VBA code that assigns a value to each of these text and numerical values by the following criteria:
If Yes, Value = 0
If No, Value = 5
If "Blank", Value = 0
If $0 to $25, Value = 1
[Code] ........
Therefore, I want to have the outputs in another worksheet (sheet 2) and it should show the following numbers based on the criteria above:
0 5 0 5 0 0 5 4 2 1
View 1 Replies
View Related
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.
VB:
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
VB:
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
Jul 29, 2013
I am building a spreadsheet for contractors to use to submit home rehab bids. I am using a Windows 8 and Excel 2013. How to assign a monetary value to a number/quantity.
For example, when the contractor indicates 2 toilets need to be replaced and 1 toilet needs to be repaired, I would like to assign the value of $300 to replace a toilet and $150 to repair. Ultimately, I would like the 2 and the 1 to be automatically totaled to read as $750 in the total line at the bottom of the table.
View 1 Replies
View Related
Oct 14, 2008
I need to pull a reference number and assign it to all records in a particular section. The code I recorded keeps going back to the first section. Also would like it to repeat until all records have the reference number attached.
06010-100Beginning Balance07/09/2004AD06011-10007/23/2004AD06011-10008/06/2004AD06011-100
06011-100Beginning Balance07/09/2004AD07/23/2004AD08/06/2004AD
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/14/2008 by acantu
'
'
Selection.Copy
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select
Selection.End(xlDown).Select
Range("C63").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
End Sub
View 9 Replies
View Related
Dec 30, 2008
I want to be able to "count" apples and oranges. Is there a way to record a particular text in a text formatted cell and count it. e.g. 10 cells, 3 say "apples", three say "oranges", 4 say "plums". I want excel to keep track of the three types of fruit when I change them and give me a running number of each.
View 2 Replies
View Related
