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.
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
I have been given a spreadsheet with the sales data for each of our salesmen. Each job has its state listed and what I need to do is break down the information by region. The catch is that the regions are not given - just the states. So I need to assign the states to their corresponding regions. To do this, I tried writing an "IF" statement like this:
=IF((B2="AL"),"SOUTHEAST",IF((B2="CA"),"WEST",IF((B2="NJ"),"NORTHEAST",IF((B2="TX"),"SOUTH CENTRAL", ... and so on.
So I was doing this for all 50 states. The problem is that the statement got too long. So how do I write this? There has to be a shorter/easier way.
I have simplified my problem to its basic parts:
Column A Contains Customer Numbers,
Column B Contains a Balance.
Customers can appear more than once, but the data is sorted by customer number.
In column C, I need to put a 1 when the balance reaches 0 or less, but only for the first instance. Everything else should be 0.
here is how i'd want it to look:
customer
Balance
1st
Trying to assign this cell's value to the correct sheet tab for the current month.
Trying this:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0),IF(MONTH(A3)=6,'[Wholesales 2014.xlsx]Jun'!$P$1,0)
A3 is todays date. Wholesales 2014.xlsx]May is a seprate sheet for the month of may and so forth for june.
The problem is the p1 value is empty for the Jun sheet so I get an error: #VALUE! (I assume that is why I am getting the error?)
Using:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0)
By itself works just fine. I need to set this up for future use, though.
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.
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?
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"?
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.
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 RelatedI 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.
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] .........
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
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.
I have over 60,000 rows of data. In one cell of each row is a "student ID". I need to determine how many times a given ID appears just once, how many times an ID shows up twice, three times etc.
View 6 Replies View RelatedI 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] .......
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 RelatedI 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
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 RelatedHere is a sample of data that I have:
code name lastname
TS19SylvainBrook
TS19SylvainBrook
TV04ValérieMusoni
TV04ValérieMusoni
VB05BenValiquette
VB05BenValiquette
VB05BenValiquette
VI02IsabelleVanasse
VR07RichardVivo
VR07RichardVivo
VR07RichardVivo
WD01DanyWilliams
WD01DanyWilliams
WD01DanyWilliams
How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee)
In this case, the desired result would be 6. (the count function does NOT give the desired result..)
I have a spreadsheet that users are filling in using a userform. Due to the fact that some data may be pre/post dated for entry I am trying to find a way that I can count the number of records per month.
What I have been trying to use (with no luck) is COUNTIF:
Is there a formula that I can use to Count how may records exist in a group.
Example. I have records labled M right, M left, N right, N left,.....U right, U left. Each letter represents a server cabinet
In a input cell, I can select M right.
In a second cell, I can select U left.
I need to have the formula calculate the number of Cabinets'
(M, N, O, P, Q, R, S, T, U) that it takes to cross the cabinets = 9
The problem I am having is that the formula has to change based on weather they select the left side or right side of the cab.
Example.
First input cell = M right
Second input cell = U right
I need to have the formula calculate the number of cabinets
(M,N,O,P,Q,R,S,T) that it takes to cross the cabinets' Note: U is not counted. = 8
I'm trying to create a userform which calculates and records the total number of hours a user puts in, however it can not exceed 40 hrs.
Dim intProject1 As Integer
Dim intProject2 As Integer
Dim intProject3 As Integer
Dim intTotal As Integer
intProject1 = txtProject1.Text
intProject2 = txtProject2.Text
intProject3 = txtProject3.Text
intTotal = txtTotal.Text
If txtTotal > 40 Then
txtTotal = txtProject1.Text + _
txtProject2.Text txtProject3.Text
txtTotal.Text = Format(txtTotal, "currency")
Else
MsgBox ["Total exceeds 40 hours."]
End If
If you guys could help me out I appreciate it
Thanks
End Sub
Private Sub lblProject1_Click()
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
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 RelatedExcel 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.
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.
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?
B20 is the number that the user enters. It can be any number and it will create new sheets below and text of sheet name in B21:F21.
If i change the sheet name below, B21:F21 would also automatically change.
I have various month's data - forex tick data - each month has between 12 and 14 million records and comes down as a .csv file.
I would like to analyse the data in Excel
How I can breakup the huge .csv file into manageable excel parts?