If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Transposing an Address List
I have a 30k line address list that looks like this:
Customer #1 ATTN: ACCOUNTS PAYABLE Street Address, SUITE B CITY, STATE ZIP CODE Customer #2 Street Address City, State ATTN: ACCOUNTS PAYABLE Zip Code I need to move all the information from one column to 5 (transpose the info so that all the names are in one column, cities in another, etc.). Does anyone know how I can do that without copying and pasting each address? |
#2
|
|||
|
|||
Transposing an Address List
It could be automated if there is some method of identifying the start and end of each address. Do all addresses have the same number of lines? Is there something that indicates the end of an address? You might be interested in code I have at http://www.cpearson.com/Excel/Variab...mnToTable.aspx http://www.cpearson.com/Excel/ColumnToTable.aspx Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 14 May 2009 10:28:02 -0700, Hank24 wrote: I have a 30k line address list that looks like this: Customer #1 ATTN: ACCOUNTS PAYABLE Street Address, SUITE B CITY, STATE ZIP CODE Customer #2 Street Address City, State ATTN: ACCOUNTS PAYABLE Zip Code I need to move all the information from one column to 5 (transpose the info so that all the names are in one column, cities in another, etc.). Does anyone know how I can do that without copying and pasting each address? |
#3
|
|||
|
|||
Transposing an Address List
Assuming that your data is in column A in fixed blocks of 5 rows each, In B1
enter: =INDIRECT("A"&ROW()*5-(5-COLUMNS($B:B))) Then copy B1 to C1 thru F1 Then copy B1 thru F1 down as far as you need. -- Gary''s Student - gsnu200852 "Hank24" wrote: I have a 30k line address list that looks like this: Customer #1 ATTN: ACCOUNTS PAYABLE Street Address, SUITE B CITY, STATE ZIP CODE Customer #2 Street Address City, State ATTN: ACCOUNTS PAYABLE Zip Code I need to move all the information from one column to 5 (transpose the info so that all the names are in one column, cities in another, etc.). Does anyone know how I can do that without copying and pasting each address? |
Thread Tools | |
Display Modes | |
|
|