Mail Labels Printing Down the Page instead of across.

Discuss the word processor
Post Reply
johndmcc
Posts: 2
Joined: Sat Apr 17, 2010 7:08 am

Mail Labels Printing Down the Page instead of across.

Post by johndmcc »

The popular Avery 5160 labels now come in an "Easy Peel" version that puts a tear-off line between the three columns of labels. When printing in a specific order - such as by ZIP code - it would be convenient to print each label DOWN a column instead of the apparent default across the page. This way peeling labels becomes much easier to keep in order.

I have experimented with the mail merge features and label building/formatting but cannot seem to find any way to enable a change in direction of the labels filling in the page.

(As a side note - this feature was a simple check box in the ancient Paradox database report builder.)

Any ideas???

Thanks
OpenOffice 3.2 on Windows 7
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Mail Labels Printing Down the Page instead of across.

Post by acknak »

Sorry, there's no check box in OOo for this. The content on the page is always processed left-to-right and then down, so that's the order that the data records go onto the page.

The only way I can think of to get what you want is to actually re-order the data records so that they end up in column order.
AOO4/LO5 • Linux • Fedora 23
johndmcc
Posts: 2
Joined: Sat Apr 17, 2010 7:08 am

Re: Mail Labels Printing Down the Page instead of across.

Post by johndmcc »

Thanks for the reply, acknak. I guess I was afraid that would be the answer.
Reordering the data to fit a report is really not feasible with a large mailing list that is constantly changing. However, this is a fairly minor thing that we can live with.
There are a number of limitations within the realm of Base, especially with reports. I wonder if anyone is doing anything with the Sun Report Builder to make it a bit more robust?
I am a big fan of the OOo suite and hope it continues to grow and improve.
OpenOffice 3.2 on Windows 7
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Mail Labels Printing Down the Page instead of across.

Post by acknak »

I don't see why it would require reordering anything.

All you need to do is add a column that calculates the (page,row,column) for the proper column-order with your label sheets, then sort the data records by that number. It may require an extra column, or maybe it can be done with a query (I don't know for sure).
AOO4/LO5 • Linux • Fedora 23
redoak
Posts: 1
Joined: Sun Mar 24, 2019 6:07 pm

Re: Mail Labels Printing Down the Page instead of across.

Post by redoak »

I think acknaka nailed it. Here's the cell formula that does the job, plus a recipe for using it:

1. Alphabetize your spreadsheet,

2. Add an extra column, and put this formula into every cell (this is the silver bullet):

="["&(10000+(1+INT( (ROW()+1-3) /(10*3))))&","&(10000+(3*((1+MOD(ROW()+1-3,10))-1)+(1+INT((ROW()+1-3)/10))))&"]"

Continue adding cells below the data until the total number of rows is a multiple of 30 (10x3). This is to make the last page come out right.

A typical entry will look like this: [10003,10025], which means the 25th label on the 3rd page (printed across, but alphabetized down).

3. Add another extra column, and copy-and-paste VALUES from the first extra column to the 2nd one. You'll probably clone the column header, too, so alter it slightly (perhaps by putting "v" at the end for "values") else the next step is ambiguous.

4. Sort the spreadsheet again, this time on the 2nd new column. And you're done.

5. Print the labels as usual. They are magically laid out just way you want them!

NOTE: This solution easily adapts to other label layouts -- e.g. 11x3 or 10x2 -- or even 4x2 (for name badges, say). The numbers 10 and 3 appear in the formula (several times). 10 is simply the number of rows and 3 is the number of columns in the labels pages.

NOTE ALSO: This solution should work equally well for OpenOffice, for all versions of Word and Excel (I tested ver. 2013), and for other MS-Office-like suites, such as Google's G-Suite Apps (which I haven't tested). Most likely it is the universal solution using just an Excel formula. Comments are welcome on that!

P.S. The 10000 might need more zeros if you're doing more than 9,999 pages of labels :-)
OpenOffice 4.1.6 on Windows 10
Post Reply