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).
102 Main Street
Dallas, TX 12345
500 Second Street
Austin, TX 12376
So I'm looking for the data to be formatted like the following:
First Church 102 Main Street Dallas, TX 12345 firstname.lastname@example.org
Second Church 500 Second Street Austin, TX 12376 email@example.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.
Dim rng As Range
Set rng = Range("A1").Resize(5)
Do Until IsEmpty(rng.Cells(1, 1))
Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True
Set rng = rng.Offset(5)
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.
Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:
10000 X Street Louisville, KY 40291 is in cell E2
I would like it to read:
10000 X Street in cell E2 Louisville in cell F2 KY in cell G2 40291 in cell H2
They also did this with phone numbers (ie. desk# / cell# / fax#).
There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.
I am work a worksheet that contain the data like that A B NUM1 Jene,Joly NUM2 Jene,Selo,Diff MUM3 Tino,Selo
this worksheet has over 1,000 data. i want get the result in sheet2 as below: A B NUM1 Jene NUM1 Joly NUM2 Jene
i write the code [PHP]Sub test() Dim i As Integer Dim arr() As String Application. ScreenUpdating = False On Error Resume Next For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row arr = Split(Cells(i,2), ",") For J = 0 To UBound(arr) With Worksheets(2) LASTROW = .Cells(Rows.Count, 2).End(xlUp).Row + 1 .Cells(LASTROW, 1) = Cells(i, 1) .Cells(LASTROW, 2) = arr(J) End With Next J Next i Erase arr Application.ScreenUpdating = True End Sub [/PHP]
the defult of this code is that it will take long time to get the result.so,i want to know how to make the code run fast.
I have a very large sheet of all US zip codes by county name. Unfortunately, the document builder decided to list up to EIGHTEEN columns of zip codes per county name... I assume to make it easier to look at.
I now need to rebuild the sheet to have one column of county names and *one* column of zip codes, which will be a nearly impossible task if I don't find a way to automate the conversion.
Attached is a sample... sheet 1 is my initial state (except here its 5 columns rather than 18), and sheet 2 is my hoped for end state. Notice that the zip codes can, but don't always, fill every column allotted.
I'm currently faced with a spreadsheet that has data formatted like this: A 1 RandomRowofData1 2 RandomRowofData2 3 RandomRowofData3 4 RandomRowofData4 5 RandomRowofData5 6 RandomRowofData6 7 RandomRowofData7 8 RandomRowofData8 9 RandomRowofData9
Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words)....
I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
I've two columns, one being the first name and the second being the last name. Is there a way that I can convert these two columns into the correct format for the email address ? Rather than having to manually do this.
Assume cell A1 contains a name and A2 contains the text "D7".
How would the code look like that 1. reads the contense in cell A2 (which is D7), 2. converts it (D7) to a cell address and finally 3. copies the content in cell A1 to the cell address found in A2 (in this case D7)
I'm trying to export to csv and then convert to address labels. I know I could do this with a macro, but I have no idea about how to write them, or even how to get it to lay out the way I want. I need to do it so I can send out all the save the dates for my wedding. I don't want to do it in word (complicated story to do with it not working with the labels I've got).
The columns are layed out like this, with example addresses: Title,Forenames,Surname,House Name/Number,Road,Area,Town/City,County,Postcode,Country Mr,Alan,Davies,23,Malvern Road,Sheperton,Leicester,Leicestershire,LE2 3BY,UK ,Jason,Robson,3,Wake Green Road,,Birmingham,,B13 9QD,....................
I am trying to take an address book on the internet and copy/paste it into excel- not hard right? Ok, so that is done. The problem I have is that there are over 2000 addresses- and they all copy straight down into one column. I know how to transpose the information- but come on- there are over 2000. that seems like a LOT of work. So my first question would be is there a way to do this all at once? For each company/name/address/phone I need one row per company.
I have a textbox with an address (ie "$C$3"), how do it then activate the correct cell (ie C3). Flash ActionScript has a function that would do this, eval(). I know VBA has a val() function, but I think thats different all together.
I have a simlple macro to add a new customer to a list using a form. One of the columns of the datra beig added is an email address. When entering the data the email address just appears as text, but i want it to be the email address link you could click on and it opens up an outlook email. Just clicking into the cell and tabbing out changes this into the format i want, but wondering how to i get the macro to do this?
If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then ..do code (I know the cell location of interest at this point) End If
Then I End up needing To Do something Like the following ActiveChart.SeriesCollection(1).XValues = _ Worksheets("RawData").Range("L1:N1")
L1:N1 above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesnt do me much good when range wants something like L1. I dont know how to convert that (12,16) to a L16 for example.
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).