Lookup Up State Abbreviations
Aug 4, 2009
I have a spreadsheet that is used for sales territories. In column A, I have customer names, in column B, I have the State abbrev., In column C I would like to return as salesperson's name based on the State.
Aco IL John Smith
ABco OH Jane Doe
Cco WI John Smith
Czco IL John Smith
Dco WY Adam Scott
Fco UT Adam Scott
FDco CT Bill Jones
etc WI John Smith
So I need a formula to enter in column C, that will return the correct salesperson based on the state in column B. (when auto filled down)
For example: IL,WI,MN,IA,ND,SD = John Smith
OH,PA,KY,MI, = Jane Doe
etc
View 8 Replies
ADVERTISEMENT
Jan 22, 2010
I currently have data in the form of: "510 E. 96th Street, Suite 400 Indianapolis, IN 46240"
And need a formula to extract only the state abbreviation from the cell, any ideas? I currently have the formula, =MID(O333,(SEARCH(",",O333)+2),2) but it only works if there is one comma for example, it works perfectly on this data:
2K MEDIcal 105 West Torrance Boulevard Suite 106 Redondo Beach, CA 90277
View 10 Replies
View Related
Jun 3, 2012
I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
View 7 Replies
View Related
Jan 25, 2010
I would like to be able to take an address like 18387 S 113Th W Ave and return it as 18387 South 113th West Avenue. I am a slightly familiar with lookup formulas, but I have not had any luck making this work.
View 9 Replies
View Related
Jan 12, 2010
writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.
e.g Company = Co. or
Insurance = Ins
Please see my attached excel document for an example.
I have over 27000 lines so doing this manually will take forever!
Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.
Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.
The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.
View 10 Replies
View Related
Jul 22, 2014
I extracted data going back to 1980 from Bloomberg and realized that BB uses abbreviations rather than pure numerical values. For example, 3,000,000 is "3M," 300,000,000 is "300M," etc. I need a macro that converts "M" into the actual value, "B" into the actual numerical value, etc. For example, a macro that converts "3M" into 3,000,000. I know that I can identify the values in VBA with "###M."
View 7 Replies
View Related
Jul 8, 2009
Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.
View 3 Replies
View Related
Jan 14, 2009
I need to get the state (xlon or xloff) of check boxes on a worksheet. i have tried the following but unable to get it to work.
View 3 Replies
View Related
Jan 16, 2009
I've got a list of numbers between 0 and 1
eg.
0.25
0.59
0.73
0.15
0.99
in column A
if I want column B to state "low (0.00-0.33), medium (0.34-0.67) or high(0.68-1.00)" for a corresponding value in column A, how would I do that?
View 3 Replies
View Related
Feb 21, 2009
Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.
Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.
My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".
If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".
View 2 Replies
View Related
Apr 11, 2008
to automate a process that split name, address, city, state, and zip.
here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN....
View 13 Replies
View Related
Jun 17, 2009
1 - If any cells (with data in it) in the "state" column contain any of the following abbreviations in it...
AB, BC, MB, PE, NB, NL, NS, NT, NU, ON, QC, SK, YT
Then all matching row cells in the column named "country" should say "Canada",
AND....(it must also meet this criteria)...
Criteria 2 - If any text in "Country" column is "Kryponite", AND "State" column is blank
If BOTH criteria 1&2 are met.....THEN...
then delete both the country & state columns
If not, keep both columns and highlight in light red any errors to this macro.
View 14 Replies
View Related
Jul 15, 2009
Are Boolean variables ALWAYS initialised as FALSE when they are created?
I am wanting to leave a 'marker' in a function so that I know whether it is the first time it has been used or not. Something similar to this:
View 2 Replies
View Related
Apr 12, 2008
I believe many people face is to automate a process that split name, address, city, state, and zip.
here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN....
View 9 Replies
View Related
Dec 21, 2008
I am looking to create a macro button which will reset the sheet to its original state.
I have locked the cells users should not imput data into, and unlocked where they add their data.
At the end of every school year, they will need to be able to reset ALL the workbooks back to their original state with all the reference formulas.
I am thinking I will need to tell the macro to create a hidden copy of the workbooks and then upon hitting reset it will use the backup to override the current. But they will need to be able to reset at the end of every year.
I would like to have one reset button that resets ALL the workbooks at once, but if not, I could put a reset button on each workbook.
As I have just started my VBA training, I have a code that will create a backup and hide it, but I have no idea how to do the reset portion. And again, since they need to be able to reset it each year for x amount of times, I don't know how to get it to keep having a fresh backup and get everything to its original.
View 9 Replies
View Related
Apr 18, 2007
Upon initial display,if my toggle control value is True, I would like it to appear "selected", or down. If the initial value is False, I would like it to appear "deselected" or up. Even though I can successfully detect the state and set the color and caption, the control always appears up, as though it is not selected.
Private Sub UserForm_Initialize()
If AppInfo_Form.debugToggle = True Then
AppInfo_Form.debugToggle.Caption = " Debugging Is ON"
AppInfo_Form.debugToggle.BackColor = &HFF00&
Else
AppInfo_Form.debugToggle.Caption = "Debugging Is OFF"
AppInfo_Form.debugToggle.BackColor = &H8000000F
End If
End Sub
View 4 Replies
View Related
Jul 20, 2007
I would be loading data into a spreadsheet, and when there is a phone number I'd like to have it populate the state field. (ex. (212) 123-1234 in the phone field, would populate "NY" in the state field, is this possible? If so is there any brief way to describe how to do so without taking too much of your time?
View 9 Replies
View Related
Apr 15, 2008
format a cell depending on the state of a check(tick) box
View 3 Replies
View Related
May 2, 2008
I have a column that has data like this in it:
cityname, stateAbbreviation, XXXXX
or
sacramento, CA 95814
The tricky part is that sometimes there's a comma delimiting these fields and sometimes there is just a space between these fields.
So, I suppose the script would have to assume a length of 2 chars for the state and when it finds that it knows that what was before that string of 2 chars was the city.
Also, sometimes there is a longer zip with 10 digits code like this:
cityname stateAbbreviation XXXXX-XXXXX
sacramento CA 95814-82202
How might I write some VB code to loop through this column and break up this cell into 3 other cells?
View 7 Replies
View Related
Jul 29, 2009
I'm playing with an excel file that has 3 different spreadsheets and basically the same layout just different product lines on each sheet. I need to find a snipet of VB code to change cell color depending on if another cell is used(filled in) or if it is blank. I'm very limited to my VB skills but can tinker enough to get what I need if someone points me in the right direction.
here is an example of what I'm trying to accomplish using the following Columns:
ItemNo[A], QUOTEREQUESTDATE[b], SUBMITTOSALESDATE[C] APPROVALRECEIVEDDATE[D], RELEASETOMFGDATE[E]
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO RED CELL:
[b] USED, [C] BLANK, [D] BLANK, [E] BLANK
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO ORANGE CELL:
[b] USED, [C] USED, [D] BLANK, [E] BLANK
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO YELLOW CELL:
[b] USED, [C] USED, [D] USED, [E] BLANK
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO GREEN CELL:
[b] USED, [C] USED, [D] USED, [E] USED
View 13 Replies
View Related
Aug 20, 2013
I would like to be able to have Excel VBA pull down the data from the following website.
The first URL will take you to the website, but you then need to select a state shown in bold from the Radio side of the index window in the middle of the screen. Once at the next screen, this is the table I wish to download into excel.
I want to do this for all the states listed. How can I do this or is it possible since you have to select a state prior to the listing.
HTML Code: [URL] ..... I found this code from anothe thread but it did not work.
Code:
Sub GetData()
Dim x As Long, y As Long, t As Long
Dim htm As Object
[Code] ....
View 1 Replies
View Related
Nov 18, 2007
I wrote the following...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("Obj1")
If Not Intersect(Target, VRange) Is Nothing Then
Range("ObjDescr1").Select
ActiveCell.FormulaR1C1 = "=IF(Obj1="""","""",(IF(EmplType=""Salary"",VLOOKUP(Obj1,SalaryDevGoalsTbl,2,FALSE),VLOOKUP(Obj1,HourlyDevGoalsTbl,2,FALSE))))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
End Sub
What it does, everytime range named Obj1 is selected, range named ObjDescr1 is populated to a Vlookup, depending on which type of employee.
I need the Worksheet Change procedure to handle Obj2 - 5. I want to Change Procedure to find out which named range Obj(n) is effected, and to do the rest on that one. ObjDescr(n) is also to match the value of (n).
How can I do this the most efficient way? I want to cut down on code in ever area possible, so I dont want to just copy and paste for obj1,2,3,4 and 5.
View 9 Replies
View Related
Jan 18, 2008
I want to write code for a Checkbox that when checked it fills a cell with a color, but if it is subsequently unchecked, the cell color disappears.
View 9 Replies
View Related
Jan 23, 2008
I have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intValue As Integer
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
intValue = CInt(Target.Value)
If CheckBox1.Value Then intValue = intValue * -1
Select Case Target
' Apples
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + intValue
Case Is = Range("X12")........................
View 7 Replies
View Related
Mar 12, 2008
I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen:
Private Sub CommandButton1_Click ()
' If CheckBox1 = marked
Call MacroA
'Else (CheckBox1 = unmarked)
Call MacroB
' End If
End Sub
View 4 Replies
View Related
Jun 14, 2008
I am having a little bit of difficulty find ways to effectively incorporate a checkbox panel into a form interface. What I am trying to do is all the user to select using checkboxes which of 31 separate columns will automatically be deleted or not deleted. The method I have been using is to create 31 checkboxes, and then use the value of the checkbox (true/false) to determine a value in an array, which then can be used to delete the column by a separate sub.
However, I am interested in being able to have the checkboxes start checked or start unchecked by values contained in a different array. My problem is that I am unable to reference the checkbox itself until the form containing the checkbox is opened, but at that point I don't know of a way to cause the desired checkboxes to automatically be selected without placing a command button that must be pressed to fill them in. Essentially I want to be able to open a userform that has checkboxes preselected.
View 5 Replies
View Related
Aug 27, 2013
I have a set of data (Data tab) that lists name, a high number, a low number, and the state that is associated with the name and range between the high and low number (see attachment). I am trying to figure out a formula that will return the state value based on a match of the name and the number between the high and low. I am trying to use Index/Match but i cant figure out the "between the high and low" portion.
tester.xlsx
View 2 Replies
View Related
Dec 6, 2006
i was wondering if there is a way to display a pop up message that a stating that the spreadsheet is currently updating.
What i have is a macro running on Auto_Open when workbook is opened. I have switch off the screen updating. What i want now is for maybe to have a pop up stating the spreadsheet is updating. Setting screen updating to false means that my excel freezes for a few seconds. I think users might be thinking that excel has crashed and therefore would like to include the message that the spreadsheet is updating its information.
View 9 Replies
View Related
Oct 22, 2007
I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.
There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.
NAME, PHONE, STATE
Bleh, 555-555-5555, =ChkState(B2)
I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field
Function ChkState(pVal As String) As Long
Dim AreaCode As String
Dim StateAbrv As String
AreaCode = Left(pVal, 3)
If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
Else
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function
I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.
View 9 Replies
View Related
Jan 18, 2008
In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
Select Case Target
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value)
Case Is = Range("X12")
Logbook.Show
Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value)
Case Is = Range("W13")
Logbook.Show.................
View 6 Replies
View Related
