Convert Address List To Multiple Columns

Apr 10, 2009

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.

View 9 Replies


ADVERTISEMENT

Convert Long Address List To Multiple Columns

Nov 22, 2007

I've been given a long list of mailing addresses in Excel in formatted as a single column, each address using three rows with no blank rows. Example:

John Doe
123 Maple St. #2
Cambridge, MA 02139
Jane Smith
321 Elm St. Apt 24
Austin, TX 34557
etc.
etc.

I need to get this data into the form ...

John Doe 123 Maple St. #2 Cambridge MA 02139
Jane Smith 321 Elem St. Apt. 24 Austin TX 34557

I would think this has been done, but I've searched the web and this forum without success.

View 3 Replies View Related

Convert Name / Address Phone In One Column To Multiple Columns

Feb 16, 2012

I have a long list of names, address, city state zip, and phone number, followed by the next name, address, etc. I need to now be able to bring each one into it's own column.

Example:
David Smith
123 Main St.
Denver, CO 12345
123.456.7890
Joe Blow
345 Happy Ave.
Oakland, CA 34567
567.890.1234

I need to convert it so that I would have

David Smith 123 Main St. Denver, CO 12345 123.456.7890
Joe Blow 345 Happy Ave. Oakland CA 34567 567.890.1234

View 2 Replies View Related

Convert 2 Column List To Multiple Related Columns

Nov 28, 2007

I have a long list of 2 columns containing data as follows:

col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3

which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....

View 9 Replies View Related

Separating Mailing Address Info From 1 Column To Multiple Columns?

Dec 18, 2007

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.

View 4 Replies View Related

Convert Row To Multiple Columns

May 20, 2007

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.

View 9 Replies View Related

Convert Single Column To Multiple Columns

Dec 6, 2007

How can I sort a single column of data to multiple columns of data. e.g. I want to convert ~1800 rows of data from 1 column to 3 or 4 columns.

View 14 Replies View Related

Convert Multiple Columns To Single Rows

Feb 27, 2007

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.

View 3 Replies View Related

Convert Multiple Rows To Columns And Add Column Headers

Oct 17, 2009

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.

View 4 Replies View Related

Convert/Transpose Multiple Groups Of Rows Across Columns

Mar 9, 2009

How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):

GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I

I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):

1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM

As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls

View 5 Replies View Related

How To Combine Multiple Columns Into Long List Of Results In 2 Final Columns

Oct 15, 2013

I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...

Current layout with varying number of data points per row...

ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5

Desired Result

ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2

and so on and on and on until all rows have been filled in down the file...

Is this possible?

View 4 Replies View Related

Convert Multiple Columns To Rows Including Blank Cells

Apr 11, 2014

I currently have a spreadsheet that I had to convert from multiple rows to columns:

[URL]

Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).

View 5 Replies View Related

Excel 2010 :: Convert Multiple Columns To Rows Depending On Input Value

Jul 6, 2014

I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:

A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910

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

A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..

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.

View 6 Replies View Related

List Values From Multiple Columns In A Dropdown List

Dec 17, 2012

how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.

This is how the table looks like.

Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -

This is how the dropdown list should look like for Product 4"

ab1
ab2
ab3

View 6 Replies View Related

Convert IP Address To IP Range

Jan 29, 2014

I have a list of random I.P. Addresses i.e. 192.168.1.0, 172.19.16.0 etc

i would like to copy them into another cell but change them to a range. i.e.

192.168.0.1-192.168.1.254
172.19.16.0-172.19.16.254

View 5 Replies View Related

Convert Names To Email Address?

Mar 28, 2014

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.

View 4 Replies View Related

Can't Convert Text To A Cell Address

Mar 12, 2010

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)

To illustrate I attached an Excel file.

View 8 Replies View Related

Export To Csv And Then Convert To Address Labels

Aug 18, 2009

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,....................

View 5 Replies View Related

Function To Convert Long Value Into IP Address

Jul 29, 2014

Any function to convert a long value into an IP address. I have an extract from a mySQL dbase that has the ip addresses in long format. I need to reverse them back to IP Addresses.

View 1 Replies View Related

How Can I Convert Web Address Book To Sheet

Jul 5, 2007

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.

View 9 Replies View Related

Convert Text Address To Cell

Apr 6, 2007

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.

View 2 Replies View Related

Convert Email Address In Cell Into Link?

Jul 22, 2014

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?

View 3 Replies View Related

Convert Cells Reference To Range Address Code

Sep 7, 2006

I have to convert a range reference in the form of

Range(Cells(1,1),Cells(2,2))

to a reference in the form of

"A1:B2"

View 5 Replies View Related

Convert Cell Number Reference To String Address

May 18, 2007

I have something like the following:

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 doesn’t do me much good when range wants something like “L1”. I don’t know how to convert that (12,16) to a L16 for example.

View 7 Replies View Related

Duplicates - Delete Every Lead On List A Whose Email Address Is Also In List B?

Jan 9, 2014

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).

View 2 Replies View Related

Splitting Address Data From 2 Different Columns Into City And State Columns?

Dec 5, 2012

I have been researching this for 3 days and I cannot find a solution. I have City, State information in A1. I also have City, State information in B1. I need to put them into City (D1) and State (E1).

View 4 Replies View Related

Dropdown List From Multiple Columns

May 28, 2013

I am trying to make a single drop-down list which contains values from two columns.

I should give you the example immediately:

NAME | SURNAME (these are two columns)
Jon | Alfa
Karl | Beta
Lilly | Wolf

And the drop-down list should look like this:

DROP-DOWN
Jon Alfa
Karl Beta
Lilly Wolf

I have already tried to solve my problem with data validation but with no success.

View 2 Replies View Related

List Box With Multiple Columns And Headings

Jan 19, 2009

I am trying to figure this out. I have a userform with one listbox with 7 columns that displays a range. It is working except i cannot get the column headings to work.

View 4 Replies View Related

List Uniques From Multiple Columns

May 4, 2007

I would like to get a unique list of letters from multiple columns.

I tried to use this formula below but it does not work with multiple columns....

View 9 Replies View Related

Drop Down List For Multiple Columns

Jan 5, 2008

I have the following in a Validation/Data the following tries to reference and array f2:j11

=if(d5="",teammember,index(teammember,match(f2,teammember,0)))

The array 'teammember' refers to cells f2:j11. Formula returns an error message that it can only refer to a single row or column. Is there a formula

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved