From Rows to

  • Thread starter Thread starter R B via OfficeKB.com
  • Start date Start date
R

R B via OfficeKB.com

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!
 
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.
 
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.
 
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?
 
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!
==============================================

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.
 
Back
Top