Cleaning Up Address List - Some Are Single Cells / Some Are Double?
Mar 7, 2013
My problem has to do with an list of over 22,000 addresses (don't want to think about going through it manually )
The names are in column A and the addresses are in column B. The problem is, is that some of the addresses fit in one cell and others are spread over two (Oklahoma City, OK in one cell, 73034 in the one below it, for example.)
The names with corresponding addresses taking up two squares have been placed in merged cells, so that they line up with their addresses. Here is a picture below.
Capture.PNG
What I need is each address in one cell next to its address in one cell (to make it searchable, etc.) I thought I was being really clever when I copied Column A, then pasted the formatting onto Column B (so that the cells would be 1:1, one name cell, one address cell) but didn't realize I would be losing the information in the cells being merged.
I have a database of sales records containing 6 address columns (C-H). I want to extract the country (which may be entered in any of the 6 columns) and place this in a separate column. I have a definitive list of countries on sheet 2 which I have saved as a named range (Country).
I would like to be able to set something up which searches the six address fields on sheet 1 and if a match is found against the Country range, enter this in a separate column (I). A non-code solution would be ideal as I am nowhere near that level yet and want to be able to understand what I am doing as far as possible!
I'm having a 'mental block' day. I have a Named Ranges in a spreadsheet (Office XP). The range is defined as =OFFSET( ' Stock List'!$K$1,0,0, COUNTA('Stock List'!$F:$F),4)
i.e. starts at K1, is 4 cols wide and as deep as there are occupied cells in col F. (as I understand it). I want to treat this as an array in VBA and use 'x' and 'y' as indexes into the array. I want to either get the value from a single cell, or set the value in a single cell, which contain a string. I know its not correct but I have the formulae as follows, and for the life of me I cant figure out what the correct formula is -duhh!!
Dim x As Integer, y As Integer, z As String x=3 'dummy test code y=2 Range("MakerExtractArea").Offset(x, y).Value = "dummy" z= Range("MakerExtractArea").Offset(1, 0).Value
I have a worksheet that contains lots of value. I want to run quick calculation on it but for some reason counta function counts blank cells.
I checked and the cells with =len() and it returns a number > 1
What is the proper way to clean the whole worksheet with some copy paste value and get rid of invisible character like space or nonprintable ones to be sure to get the right number of cells with value?
I am trying to create an easy to use document that allows users to click a button to add or subtract to another cell in my document. I have the basic functionality working, but am now working on making this more fool proof.
Basically what my macro does now is when user doubleclicks C1, 1 is added to B1. When user doubleclicks D1, 1 is subtracted from D1.
What I'd like to do is to protect C1 and D1 so that they can only be clicked to add or subtract, and not clicked as to edit the individual cell content (C1 and D1). B1 should still be unprotected so that the user can manually enter a number.
If possible I'd also like to change the below macro so it works with single clicks instead of double clicks, but this is not essential.
My document consists of several worksheets, and right now I am using the macro on each individual worksheet. If there is any way of modifying the macro so it works with the entire workbook instead of having a copy with each worksheet, that would be nice as well.
Code: Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) If (Target.Row >= 1 And Target.Row
I have a list of ID's on one sheet that are also located in a large matrix on another sheet.
This macro uses the Find_Range function to find each ID within the matrix and return the column header where the ID was first located. There is also a line which return a list of ranges, indicating all the places where the ID was found.
What I want is to convert the list of ranges into a list of corresponding column headers (ie row 1 of all columns in the range)
' Number of id's in list RowCnt = Application.WorksheetFunction. CountA(Columns("A"))
For I = 2 To RowCnt
LookFor = Sheets("ID List").Range("A" & I) Set InRng = Sheets("Matrix").Cells Set Found = Find_Range(LookFor, InRng, xlValues, xlWhole) On Error Resume Next ' If value is not found Sheets("RateID Count").Range("C" & I) = Sheets("Matrix") _ .Cells(1, Range(Found.Address).Column) ' Return column header Sheets("ID List").Range("D" & I) = Found.Address(False, False) ' Return cell address or range of addresses On Error Goto 0
I recieve a spreadsheet each month with the following column of data:
9 Paddington House PO Box 73 Whiteleys of Bayswater 9-10 Grove Road 134/138 Drymen Road 44-46 The High Street 1 Midland Road 48 Donegall Place
As you can see, it consists of addresses, each line is a separate address. I need it to be converted so that the data is split into the following 5 fields:
Street number from street number letter from street number to street number letter to street
If there is no street number , then it should just enter the data into the street field. If there is a range such as 44-46, it should enter the 2 numbers into the from and to fields. Sometimes this is in the format 44/46. Also, sometimes there are letters, eg 44a-44b, in which case each section (ie 44, a, 44, b) needs to be in a spearate field.
I am working on an email marketing project and i have a small problem. I have two different email list. One (List A) is a large list of potential leads. The other (List B) is a list of leads we are not supposed to market to. I need to delete every lead on List A whose email address is also in List B, so that we do not send unwanted emails to our clients.
The best way i have to do this so far is to go through line by line, which is very impractical.
In case it matters here is out list format. Each list has 10,000 + leads. Each lead occupies a row. The row stretches across 13 columns and each column holds a different variable about the lead (names, state, email address).
I have 2 columns with data. The first one has 16000 data (6 digit numbers), which are 2 or 3 or 4 times the same (duplication). The second column has a list with name folders.
Question: How can I delete the duplicate/triplicate entries, and leave only the one I want, according to a priority list I have of name folders?
Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.
I am using this peice of code from another post in order to fill a ListBox with Outlook addresses. It works fine, but the the list has thousands upon thousands of names and it takes forever to run. I thought I'd add code to only add specific entries based on what a user inputs to a TextBox, but it doesn't seem to speed-up the process. how I can get to the user-defined data more quickly?
Private Sub CommandButton2_Click() Dim olApp As Outlook.Application Dim olNamespace As Outlook.NameSpace Dim olItem As Outlook.AddressEntry Dim olAddressList As Outlook.AddressList Dim olAddressEntry As Outlook.AddressEntry
I am basically doing some housekeeping and trying to reduce my number of code lines. Can this bit of code be done a bit simpler?
For r = lrow To 15 Step -1 If Range("N" & r).Interior.Color = vbRed Then Range("N" & r).Interior.Color = vbCyan Range("N" & r).Value = "Triangular" End If If Range("V" & r).Interior.Color = vbRed Then Range("V" & r).Interior.Color = vbCyan Range("V" & r).Value = "Triangular" End If Next r
I'm trying to copy a list of addresses from a website and paste that into Excel. I'm having problems because Excel doesn't separate each address. The original website address list has clear separation of addresses (returns, tabs, etc). For example:
John Doe 111 Fox Street Van Nuys, CA 91403 (See there's no separation between addresses) Michael Smith 2759 Apple Street Encino, CA 93727
This is the website where the address list comes from: [URL]....
I am using VBA to enter customer information into a Microsoft SQL database. I have an excel sheet with thousands of customers listed. I will be using a While loop to process each line of the sheet. Part of the While loop needs to make any format changes necessary before the data moves over to the DB. So here is the issue:
Into a uniform layout? I would prefer ###-###-####
Each of the numbers above is an example from the list I have. There may be more variations (I have over 20 seperate lists to process, with over 10000 customers per list)
I imagine I need some process that will remove all character except numbers and then break the numbers up to add the hyphens back in at the appropriate places.
I frequently use the record option to start off my macros. I realize this usually results in recorded events which are not absolutely necessary (recording a print macro is one that is full of excess line items). I then try and combine what I have recorded with what I have garnered from this wonderful website. If my spreadsheets are small - and I am not writing code to pass any inspections - is there a need to go back and clean up?
I am trying cut and paste a list into Excel, and have Excel separate the different fields for me. There are 12 lines per each entry on the list. I use Firefox as my browser, and when I cut and paste using Firefox into Excel, this is how it looks:
James H. Patel, MD (space) Pediatrician (space)[code]....
I'm hoping for the final product to look like this:
Last Name----First Name----whether MD or DO----Street Address----City, State, Zip code----Zipcode(by itself)
I am trying to see if an email address matches with a list of names in a worksheet. If so, it would proceed with code. For example, I have an email address 'middlesburg.john@gmail.com' that we get through an input box and in the list, we find John Middlesburg. Since it is obvious that John Middlesburg is middlesburg.john@gmail.com, how can I get it to go on through?
The above is basically an action list showing who needs to do what. Each row in the list has a task and an assignee and the assignee's email address
I want a macro (that will be attached to a button or text in column H) that will copy that entire row (as well as the headers in ROW 4) and email that info via the default email application being used on the users PC (most likely to be MS outlook). i.e if i click on the button or text in column H for Task A (Row 5), an email will be sent to JB@excel.com (column G), for Task E (Row 9), an email will be sent to TB@excel.com,...etc.
I would prefer it if the info was sent as the actual message in the email instead of an excel attachment.
I'm looking for a way to convert an old church address list that is formatted in a single column. There are no blank rows, but the amount of rows for each entry can vary. For instance, there could be as little as 3 rows of data (church name, address, city/state/zip) or more if email addresses and websites are provided.
The data is currently in the format below (notice how one entry has a website while the other does not).
First Church 102 Main Street Dallas, TX 12345 email@whatever.com Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com
So I'm looking for the data to be formatted like the following:
First Church 102 Main Street Dallas, TX 12345 email@whatever.com Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com
I was able to find the following code from a Google search, but it can't dynamically adjust the range.
Sub x() Dim rng As Range
Set rng = Range("A1").Resize(5) Do Until IsEmpty(rng.Cells(1, 1)) rng.Copy Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True Set rng = rng.Offset(5) Loop
End Sub
I think what I need to make this code work is a way to dynamically adjust the range so that it can determine when to move to the next row of data. Static ranges break the process due to the amount of data being provided not being uniform. What I was thinking is that I could use the word "church" as a start point and end point in a loop so the script knows when to jump to the next row and begin copying the proper number of columns. I'm just not sure how to accomplish this in vba.
I'm curious if there's a way to use the Vlookup function to compare TWO values in TWO cells with two values in two OTHER cells (same row though) and return one value from another column. Something like
Vlookup(A2 & C2, Sheet2!A1:C400, 3, FALSE & FALSE)....? Does that make sense?
Say the values in A2 and C2 are in A24 and B24 respectively in Sheet2. Can it return C24 for me?
