printing mailing labels when data is in rows

[Britt3]

I have a spreadsheet that displays my customer data in rows like a mailing
label.
1 Customer name
2 Customer Address
3 City, State Zip
4
5
6 Customer Name
7 Customer Address
8 City State Zip
9
10

All the data is in one column and the data format repeats every 6th row. So
I have five rows of name and address data for one entry then five more rows
of data for the next entry and so on. How can I reformat data into columns
for mail merging or print to mailing labels using the current layout?

Britt3

 

[Gord Dibben]

Enter this formula in B1.

=INDEX($A:$A,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)

Copy across to F1

Select B1:F1 and copy down until you get zeros.

Select all and(in place)copy>paste special>values>ok>esc

Or use this macro..........

Sub ColtoRows()
Dim rng As Range
Dim I As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = 5 'or use InputBox("Enter Number of Columns Desired")
For I = 1 To rng.Row Step nocols
Cells(J, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A") _
.Resize(nocols, 1))
J = J + 1
Next
Range(Cells(J, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP

 

[Bruce Sinclair]

I have a spreadsheet that displays my customer data in rows like a mailing
label.
1 Customer name
2 Customer Address
3 City, State Zip
4
5
6 Customer Name
7 Customer Address
8 City State Zip
9
10

All the data is in one column and the data format repeats every 6th row. So
I have five rows of name and address data for one entry then five more rows
of data for the next entry and so on. How can I reformat data into columns
for mail merging or print to mailing labels using the current layout?

You might be able to use the word 'mailmerge database' instead by copying
and pasting your data from XL ? I looked at doing something like that
<mumble> years ago when I first started making labels, so it's all a bit
vague now, but I think it's like using an internal word data source ?
Perhaps a similar question in the word newgroup might get a more helpful
answer ?
Long term (ie if you need to do this more than once or twice ... or if your
data is likely to grow ), I suggest that reformatting your data is
probably the best option.

Best of luck.

 

[Britt3]

This helped me tremensously!

Thank you!

Enter this formula in B1.

=INDEX($A:$A,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)

Copy across to F1

Select B1:F1 and copy down until you get zeros.

Select all and(in place)copy>paste special>values>ok>esc

Or use this macro..........

Sub ColtoRows()
Dim rng As Range
Dim I As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = 5 'or use InputBox("Enter Number of Columns Desired")
For I = 1 To rng.Row Step nocols
Cells(J, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A") _
.Resize(nocols, 1))
J = J + 1
Next
Range(Cells(J, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP