Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
printing mailing labels when data is in rows
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
printing mailing labels when data is in rows
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)copypaste specialvaluesokesc 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 On Tue, 4 Nov 2008 17:37:00 -0800, Britt3 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
printing mailing labels when data is in rows
In article , ?B?QnJpdHQz?= wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
printing mailing labels when data is in rows
This helped me tremensously!
Thank you! "Gord Dibben" wrote: 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)copypaste specialvaluesokesc 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 On Tue, 4 Nov 2008 17:37:00 -0800, Britt3 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Mailing labels from rows to columns | Excel Worksheet Functions | |||
Printing Mailing labels...already have full address in each cell | Excel Discussion (Misc queries) | |||
Printing mailing labels | Excel Discussion (Misc queries) | |||
How do I get data list to print mailing labels? | Excel Discussion (Misc queries) | |||
how do i convert MS Word mailing labels into an Excel mailing lis. | Excel Discussion (Misc queries) |