Hi, I made a spreadsheet of addresses with the rows formatted across as:
name, address, city, state, zip.
name2,address2,city2,state2,zip2
However, now I would like to change the format to

name,
address,
city,
state,
zip
<space
name2,
address2,
...
...
of each person.

Does anyone know how to do this? Thanks!

--
Message posted via http://www.officekb.com

One way:

In a second sheet (assuming your list is on Sheet1), enter in A1:

=IF(MOD(ROW(),6),OFFSET(Sheet1!$A$1,INT((ROW()-1)/6), MOD(ROW()-1,6)),"")

Copy down as far as necessary. Select column A. Copy it. Choose
Edit/Paste Special, selecting the Values radio button.

In article ,
"R B via OfficeKB.com" wrote:

Hi, I made a spreadsheet of addresses with the rows formatted across as:
name, address, city, state, zip.
name2,address2,city2,state2,zip2
However, now I would like to change the format to

name,
address,
city,
state,
zip
<space
name2,
address2,
..
..
of each person.

Does anyone know how to do this? Thanks!

Thank you!

Excuse my ignorance, but how could you copy this command all the way down without pasting each time?

Also, would you happen to know if word's mail merge has a bug? I tried importing these to mail merge, but it says that word is unable to open the data source.

--
Message posted via http://www.officekb.com

Copy down to 6 times the number of current rows. Or paste the formula
into all those rows. No difference.

In general, Mail Merge doesn't have a bug. But if you're using the file
as the source for a merge, I'd recommend leaving it in rowss. Without
knowing which versions you're using, and which OS, it's hard to even
take a guess what the problem is. Did you save the XL file?



In article ,
"R B via OfficeKB.com" wrote:
Excuse my ignorance, but how could you copy this command all the way down
without pasting each time?

Also, would you happen to know if word's mail merge has a bug? I tried
importing these to mail merge, but it says that word is unable to open the
data source.

If the reason you're trying to change the format of your list is to use it
in MailMerge, and you think that the vertical format will work better on
labels or envelopes, IT'S NOT SO.

Leave your data list in it's proper form, and MailMerge will work just as
good, if not better, with your present configuration.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"R B via OfficeKB.com" wrote in message
...
Thank you!

Excuse my ignorance, but how could you copy this command all the way down
without pasting each time?

Also, would you happen to know if word's mail merge has a bug? I tried
importing these to mail merge, but it says that word is unable to open the
data source.

--
Message posted via http://www.officekb.com