VLOOKUP With Zip Codes
Feb 10, 2009
VLOOKUP returns incorrect results when comparing zip codes across worksheets. I think the problem could be caused by 2 things:
1. There are duplicate rows in the lookup_value range because each represents an individual and needs to remain a duplicate to show whether there are multiple individuals from the same zip. In these cases, VLOOKUP only returns the first matching zip from rows containing duplicate zips. (See 07042 in worksheet 1 of the file linked below.)
2. Zip code format is not being treated as a number during sort. Do I need to use a different format during sort? Don't want to lose the leading zeros on the zips.
View 9 Replies
ADVERTISEMENT
May 5, 2006
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
View 3 Replies
View Related
Feb 26, 2009
I have two columns, zipA and zipB. There are over 7000 rows and I need to calculate the distance between zipA and zipB for each row (so the result should be over 7000 rows of distances (in miles)). I posted this in the programming board because I figured it would require some. I don't have any add-ons.
View 14 Replies
View Related
Jun 9, 2013
Just starting to learn VBA. I have the 2 following codes.Can they be combined into 1 code.I have 2 buttons one say hide rows and the other button says unhide rows....can 1 button be used and when the rows are visible the button says hide rows and when the rows are hidden the button says unhide rows
Sub hiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = True
End Sub
Sub unhiderows()
ThisWorkbook.Sheets("sheet1").Rows("3:12").Hidden = False
End Sub
View 6 Replies
View Related
Feb 24, 2007
At my last job we purchased a zip code addin from spheresoft that could give you a distance between two distance ie: =ZipCodeDistance(zip1, zip2)
This worked well but I am now at a new job and wondering if there is any data out there that could help me build my own sheet. I just need California
I have both zip codes now just need the distance, I could sit down, drink a ton of coffee and enter zip codes all night into Mapquest but am hoping for a better way.
View 9 Replies
View Related
Mar 22, 2007
I have a spreadsheet containing data in coloumns A to L.
Where all the codes in column B are the same then a border must be placed around all the codes that are the same i.e the border must start in Column A and end with column L.
provide me with the code the will put borders around the codes in column B that are the same ...
View 9 Replies
View Related
Apr 1, 2008
I have written a VBA code.
I thought I could just save it as an ADD-IN and then have my colleagues to add it, but that does not work. The macro will be stored in a seperate workbook and that is not what I want. I want them to be able to open any workbook and want them to be able to run the macro. I.e. everytime they open a workbook the macro should be available and be ready to run.
View 9 Replies
View Related
May 28, 2009
I am looking out for a macro by which i can combine multiple PDF's into one through excel using vba codes.
Please note that:
> all these PDF's are in one folder
> the code needs to be dynamic (i can have an option to combine selected PDF's
> i am working in excel 2007
> i have acrobat 8.0 installed
View 9 Replies
View Related
Oct 2, 2009
I have names in the following format in Column A: e.g.
Mr. Doe SmithSmith DoeJr. Doe SmithJr. Smith DoeMiss Abc XyzMrs Abc XyzXyz Abc
I want a VBA code that would turn all different formats of names listed above as:
Doe Smith
Abc Xyz
I also want another code that would turn above formats as follows: (by the last name)
View 16 Replies
View Related
Oct 23, 2009
I have a database where each subject has data for 4 days and a number of different codes each day. I am looking to calculate how many different codes they have on day one, how many unique codes they have on each subsequent day and the sum of all these codes.
For example, in the data attached:
Subject 10002 = 2,1,0,0 (3)
Subject 10005 = 3,1,0,0 (4)
Subject 10009 = 3,1,1,0 (5)
View 12 Replies
View Related
Nov 17, 2008
I have an excel workbook with two worksheets. The first worksheet contains a lot of data with more than 20.000 rows and 20 columns. The important part is that there are two different columns (column F and I) containing codes of items. What I would like the macro to do is the following:
write codes of items in the second worksheet column B under each other. I would like the macro to lookup the codes from this column in the first worksheet (column F and I) and if it finds then display in worksheet 2 column C the column in which found it on worksheet1. So basically I write on worksheet2 column B many many codes and then I press a button to do a search and displays next to the items (in column C) if it found the code in worksheet1 column F, or column I or both, or did not found it.
View 2 Replies
View Related
Jun 8, 2009
I have the following seven codes used to fill a ListBox with data retrieved form a range (DACNRange).. they are almost the same .. the only difference between them is the range column number (highlighted with red). Instead of these seven similar codes, Can we create a general code replaces them all?
View 2 Replies
View Related
Jan 12, 2009
City, state 29785
city, state 29767
city, state 29719
I need the zip codes to be pushed to the next column, to column 'D' to be exact. I already tried using the "text to columns" function. I selected "delimited" file type and used "2". it pushed the data to the next column everytime there was a 2. The problem is, there are 2's in the middle of the zip code sometimes. Also, it gets rid of all the 2's. Also, I can't use the "fixed width" option because there are some city/states that have more letters in them obviously, so the column is not a perfect width.
View 8 Replies
View Related
Dec 30, 2012
I am just thinking if there is a way to delete or hide all vba codes as I dont want readers to see them.
View 4 Replies
View Related
Apr 18, 2013
I have a spreadsheet that has Zip Codes by Service Days, and then I have another Spreadsheet Zip Code Ranges and the Zone they fall in. I want find the Zip Code associated within that Zone for the Zip Code. I listed some data below.
Zip Code Spreadsheet
State Destination Zip Service Days
NY 12095 2
Zone Chart
From Zip
To Zip
Zones
[Code].....
View 5 Replies
View Related
Jan 3, 2014
How to merge two codes too allow (2) functionalist
I have a rather complex situation that I'm not sure how to handle. First let me provide some background on what I have accomplished thus far.
Background: I have an excel file with two worksheets. Worksheet two, named Data, is where all my data is located. It begins with A1 going through JC4 (A1:JC4). However, for the purpose of my project the data I'm concerned with starts on E1 through JC4. Also, every other column is formatted with ="".
I have used the following sequence of commands, obtained from a website, to make it much simpler to define name ranges:
Select the worksheet "Data"Select cell A1Select the current region using CTRL + SHIFT + 8Press CTRL + G to bring up the Go To dialog boxClick on the "Special" button to bring up the Go To Special dialog boxSelect "Constants" and press the ENTER key (or click the "OK" button)Now define the Named Ranges using your selection by pressing CTRL + SHIFT + F3Excel will ask you to "Create names from values in the:" Make sure only "Top row" is selected, then press the ENTER key (or click the "OK" button)Check that the correct named ranges have been defined by opening the Name Manager with CTRL + F3 While I have defined named ranges with the above steps, this is not the same as a dynamic named range. At least I don't think its the same thing.
I have also protected the worksheet to prevent anyone from changing the formatting. Note: the top row of each column that contains information has been designated as the name for the information that comes after it (see step 8 and 9 above). Now, my first worksheet is where I have created conditional drop down screen by using data validation. The drop down screen are conditionally formatted. Meaning depending on the selection from the drop down screen in cell E27 will determine the list on the drop down screen in cell F27, and so on through cell H27. Furthermore, the drop down items on:
1) Cell E27 (in the first worksheet) are located on the second worksheet in cells E1, G1, I1, K1. Each of those columns have a list of items that fall under them.
The lay out of the data worksheet is too combursome to list out, but I have attached a picture. IF you follow the data you will see some of the information is listed in two cells, for conditional formatting purposes. I have also attached a picture of worksheet 1 with the drop down screens.
Code 1: (allows the selection of multiple items in one cell separated by a comma)
Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
[code]....
Code 2: (allows for manually adding directly to the drop down list a new item that is not on the list. It will then add it to the list after the hard entry directly to the drop down menu). Remember the list will be on the Data worksheet, worksheet #2. The drop down menus are on the first worksheet).
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
[code]....
I have added the first code by right clicking on the worksheet and viewing the code, then adding Code 1. It works beautifully.
What I'm trying to figure out is how to add Code 2 to the mix with out unding Code 1. I'm trying to either combine the codes or insert the second code without disrupting Code 1. Remember I have already created a defined name range in my Data worksheet for all my columns/information. I tried deleting Code 1 and only inserting Code 2 but it didnt work. Code 2 does not work based on how I have designed the formatting. How do I apply Code 2 to what I have done thus far? Can this be done by bypassing a dynamic name range, since I have already assigned a defined name range? I want both codes to apply to Columns E, F, G, and H on the first worksheet.
View 1 Replies
View Related
Feb 7, 2007
I have a list of UK Post Codes that I wish to filter but because of the format I'm getting problems.
The reason is that I'm only interested in the first part of the post code.
Example: Using IP7 6NH and IP32 8LR. If I tried to filter those using greater than then Excel would treat IP7 6NH as larger then IP32 8LR because 7 is larger then 3.
The post codes are all imported from another piece of software and are always in the format of 3 or 4 chracters, then a space and then 3 more chracters. I wish to create another column which only has the first part in it. So I wish to crop IP7 6NH down to just IP7 and IP32 8LR down to just IP32.
Then I want to make a further column which looks at the value in the first new column and returns "In" or "Out" based on some conditions.
These are the conditions. If the cell begins with CO or NR or CB or CM or PE, regardless of the rest of the value, then "Out" should be returned. If the value is between IP1 and IP6 inclusive then "Yes" should be returned.
If the value equals IP7 then "No" should be returned
If the value is between IP8 and IP17 inclusive then "Yes should be returned.
If the value is between IP18 and IP29 then "No" should be returned.
If the value equals IP30 then "Yes" should be returned.
If the value is bewteen IP31 and IP33 inclusive then "No should be returned"
I'm think that it's quite complex and maybe the way to go would be to have two other columns, one containing the "In" values and the other containing the "Out" values, that a formula can refer to when making it's yes or no choice but I don't know how to do that.
View 9 Replies
View Related
Feb 27, 2007
I need a macro that displays a msg box when the user hit Delete on the keyboard. "The msg box will display/
"Are you sure you want to delete the selected information? This information might not be recovered.
If you delete it now the undo button in the toolbar could recover the information if activated prior to any other input.”
View 9 Replies
View Related
Nov 23, 2008
I have sheet from 1st to 31st of the month wherein I feed the employees details in codes or legends. I need to count certain codes as detailed below: ....
View 9 Replies
View Related
Feb 21, 2009
I created a financial forecast model from scratch. The model is self-contained and has no external links.
Backgrounder
Before firing a question at you, let me explain briefly how the model works. I have an assumptions tab, several costs tab from various departments that ultimately feed the Results tab (target price on a monthly basis) . Let's assume that my target price is on line 200. To me more specific, for Jan09, the target price is on B200, Feb09 on C200 and so on.
Management will feed the model themselves, given that they are assigned specific areas that they can input their forecast.
Issue or question to you
I would like to track historical changes to the price on line 200 on my target page. That is for every individual change and their impact on price.
To illustrate this let assume the following:
Let say User A made several changes on his forecast, I would like the macro to automatically record the individual change that has been made and the impact it had on the target price on line 200.
Assume that User A made two changes on his forecasting sheet. He increased the cost of supplies by $50,000 (on one cell) and increased the cost of labour by $10,000 (on another cell).
What I would love to have is a macro that would record the changes that User A changed; (i.e. cell, tab, user) after he entered the $50,000 and after he entered the $10,000. This would be recorded on a separate sheet.
Thus, immediately he entered the $50,000 on the cell and press it would record the change and the impact it had on price on the target page. Then when he entered the $10,000, it would do the same.
The format of the "changes sheet" that would contain the changes would look something like this:
USER..Date..Cell changed...Tab..From value..To value. Price Before Price after Impact (Price before less price after)
I am aware that there's a lot of details but if you don't understand the issue, it's very difficult for you to resolve.
I assume that it requires a VBA code of some sort to set this up. Given that I am not familiar (a true dummy) with VBA, I would need like a step by step instruction.
View 9 Replies
View Related
Apr 14, 2009
Every day for each date of month I have to copy data from mails to my workbook and a last sheet named report in which I need last row value from 7 or 8 sheets for this I have developed following codes which seems to me very long procduer.
Code: ....
View 9 Replies
View Related
Apr 21, 2009
I have "All revenue sheet ",
All Revenue
ABCDEFGHIJK1Sheets Name2UB Revenue4/1/2009115050022009894757619017303IB Revenue4/1/2009115050022009894757619017304SD Revenue4/1/2009115050022009894757619017305KU Revenue4/2/20093184081201027382.4771438035134.46KU Revenue4/3/2009115050022009894757619017307KU Revenue4/4/2009115050022009894757619017308KU Revenue4/5/2009115050022009894757619017309UJ Revenue4/1/20091150500220098947576190173010UJ Revenue4/2/20091150500220098947576190173011UJ Revenue4/3/200911505002200989475761901730
Excel tables to the web >> Excel Jeanie HTML 4
fo which I have developed following codes taking help from Jindon and Krish Codes , which works but a problem is that there is a creteria in Query Sheet in column C, If I write City Name with St Date and En Date it displays result and If I leave C2 blank and only write Strat Date and End Date , it shows nothing, however it shows all cities data from mentioned dates, which to be done by Advance Filter.
HTML Sub Query_1()
Dim a, i As Long, w(), n As Long, LR As Long, c As Long
Dim fDate As Date, sDate As Date, City As String
a = Sheets("All Revenue").Range("A2").CurrentRegion.Resize(, 11)
sDate = Sheets("Query-1").Range("a2").Value
fDate = Sheets("Query-1").Range("b2").Value
City = Sheets("Query-1").Range("c2").Value
View 9 Replies
View Related
Apr 28, 2006
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("e273:g284")) Is Nothing Then
Set rng = Range("e273:g284")
ElseIf Not Intersect(Target, Range("g273:j284")) Is Nothing Then
Set rng = Range("g273:j284")
ElseIf Not Intersect(Target, Range("j273:l284")) Is Nothing Then
Set rng = Range("j273:l284")
End If
Application.EnableEvents = False
If Not rng Is Nothing Then
If Application. CountIf(rng, Target.Cells(1, 1).Value) > 1 Then
MsgBox "This vehicle is booked out at this time"
Target.ClearContents
Target.Cells(1, 1).Select
End If
End If
Application.EnableEvents = True
End Sub
the code is perfect for what i need it to do but the only problem i have is that the codes roll on from each other... I.E:- E273:G284 - G273:J284 - J273:L284. first ends in G second starts in G, Second ends on J third starts on J. for some reason this doesnt work, the first code gets the prority and works but the second works in all the columns except the first one.. in this case the first code is fine, second actually works from H not G and third works from K not J
View 2 Replies
View Related
Oct 29, 2006
I am generating Barcodes in Excel with the Barcode Symbol Code 39.
When i enter the desired number in the cell, the Barcode for the entered number gets automatically generated on the previous cell.
eg: Barcode (A3)
Number (A4)
Empty cell
Barcode (A6)
Number (A7)
Now i want to generate a sequence of barcodes starting from so and so number and ends in so and so number.
Now the way i do is manually enter each number one by one which is a tedious work.
View 9 Replies
View Related
Feb 13, 2007
I have to create item code (col B) based on emp id (col Q). If emp id 9999 appears 3 times, the item codes should be 9999_1, 9999_2, 9999_3. If Emp id appears only once, item code = emp id. How do I achieve this?
View 6 Replies
View Related
Aug 11, 2007
Is there a way I can set up the worksheet so that in one cell I can type out for example product codes/numbers and by doing the description for the product will come up in another cell. Rather then copy and pasting very the many different product descriptions?
View 2 Replies
View Related
Jul 7, 2013
I have these two codes for two different buttons and was wondering if it were possible to combine them into one button?
private sub commandbutton1_click()
dim i as long
i = 2
for each cell in sheet12.range("b:b")
if cell.value = "d" then
[Code] .......
----
private sub commandbutton2_click()
dim i as long
i = 1684
for each cell in sheet12.range("b:b")
if cell.value = "f" then
[Code] ......
View 5 Replies
View Related
Sep 14, 2013
I am looking for codes to clear the textbox fields while the existing codes transfer the information to the execel page. I have programmed it to transfer the data to the sheet but I can't write the codes to clear the textboxes for the next entry.
I want to program the same command button to transfer the data to the sheet and clear the textboxes simultaneously.
VB:
Private Sub CB2_Click()
Unload UFH1
End Sub
Private Sub L1_Click()
[Code].....
View 5 Replies
View Related
Aug 17, 2014
I have a large address worksheet in sheet1.
I want to have the print preview button, a save as PDF button and a few more commands on sheet2.
How can I get the buttons in sheet2 to save as pdf sheet1?
View 3 Replies
View Related
Aug 4, 2009
Is there a simple way to find all rows in a 25,000 row worksheet, that contain zip codes from another, smaller, worksheet.
View 9 Replies
View Related
