Excel address data - single column to rown

Microsoft Excel 2007
February 11, 2010 at 02:37:49
Specs: Windows XP Pro, na
I have an excel worksheet with hundreds of
addresses in a single column, each address
separated by a single row. I need to get the
addresses into mail-merge format so each
address (which ranges from 5-7 rows in the
single column) ends up on a row. I need the
country of each address to end up in the same
column. I am struggling badly with this! Can
anyone help? Currently there are two columns
to the left of the address column. Each column
represents a book version - English & Spanish
and below these headers is a qty of each type
each address has to receive. I need to retain
the qty per address in the output file!

See More: Excel address data - single column to rown

Report •

February 11, 2010 at 06:04:01
re: each address ... ranges from 5 - 7 rows in the single column

That's the issue that concerns me. If one "address group" looks like:

1 - Name
2 - Address
3 - City
4 - State
5 - Zip

and another looks like

1 - Name
2 - Address Line 1
3 - Address Line 2
4 - City
5 - State
6 - Zip

and yet another looks like:

1 - Name Line 1
2 - Name Line 2
3 - Address Line 1
4 - Address Line 2
5 - City
6 - State
7 - Zip

Then it would be difficult for a macro to simply loop through each address group and rearrange the data.

However, if there is something in each address group that the macro could look for, then things get a little easier. Perhaps the code could use the 5 digit zip code to determine that is at the bottom of an address group.

Please give us a couple of examples of address groups so we can see how the sheet is laid out. Please include the location of the extraneous data that has to be moved with the address group.

Report •

February 11, 2010 at 06:21:27
Thanks for responding!

The company name is the first line of every address and is in
bold. We could delete all fax and phone numbers manually
(still painstaking though) as there are sometimes a phone
number or a fax number or both with the objective of making
the country the last line of every address.

Here is a sample of the live file as it is.

Fisher Scientific (Austria) GmbH
Rudolf von Alt-Platz 1
1030 Wien
Tel: 0800 20 88 40

LABOR Partner
Linzer Straße 451a
1140 Wien
Tel: +43 1 57660

Lactan Chemikalien und Laborgeräte
Vertriebsgesellschaft m.b.H: & Co.KG
Puchstraße 85
8020 Graz
Tel: +43 1 316 323692-20
Fax: +43 1 326 323692-21

Report •

February 11, 2010 at 10:07:46
As long as each entry for Tel: and Fax: start with those strings, the deletion of those line can be easily automated.

After that it looks like the only difference is that some address groups have 2 lines for the company name and some don't. Can you confirm that that is consistant throught the file?

You also mentioned something about needing to bring quantity data along with the address. Can you expand on that by telling us where it is located in relation the address and where it needs to end up after the addresses are moved to columns?

We're getting close!

Report •

Related Solutions

February 11, 2010 at 12:11:58

Another option to help get the country in the correct column, would be to have a list of countries. As the macro runs, if the cell contents exist in the country list, they are copied to the correct column for country.

After a Country has been identified, assume next row or rows up to next blank row are phone numbers.


Report •

February 11, 2010 at 13:33:19
That's kind of where I was heading...I was just waiting to hear if there were any other "layout" issues we might need to know about.

Since the OP is looking for a mail merge format, I'm assuming that each peice of data needs it's own column, not just the countries. I was planning on using the countries (based on a list in either Excel or VBA) as the delimiter for each address group.

Of course, if every address group contains a Tel: line then we could key off of that in both directions, looking for the blank lines. No country list would be required.

However, I think we need to know where the "extra lines" in some groups come from. If there are 2 lines for the name in some groups, 2 lines for the street address in others, etc. things get a bit more complicated.

Report •

February 12, 2010 at 01:31:28
Thanks for your help guys. This is great. There are currently 2
columns to the immediate left of the address column and these
contain quantities of English version book (1st column) and
Spanish version (2nd column). Happy for these to remain to the
left of each output address.


Report •

February 12, 2010 at 04:21:26
You haven't answered the questions in Response #3.

Details, my friend, details. I'll try again:

1 - Where are the quantities located? Are they next to the first line of the address group? The second? Third? A mixture?

2 - Ignoring the Tel and Fax data for now, one address group in your example has 5 lines in the address, the others have 4. The "extra" line appears to be part of the company name. The question is: When there are 5 lines in an address group will the extra line always be part of the company name?

Report •

February 12, 2010 at 04:27:54
Ha, quite right. Consider me chastised!

1) The quantities appear on the same row as the company
name, always - but in the 2 columns directly to the left of the
address column

2) No, the company name is not always responsible for the
additional row. Here are other examples...

OneMed AS
Skårersletta 55
1473 Lørenskog
Postboks 413
1471 Lørenskog
Tel: +47 67 92 27 00

VWR International AS
Kakkelovnskroken 1
PO Box 45 Kalbakken
N - 0901 Oslo 1
Tel: +47 02290

ul. Wiertnicza 124
02-952 Warszawa
Tel: +48 22 646 7726

Report •

Ask Question