SNAKECOLS, How to snake columns to use fewer pages

SNAKECOLS, How to snake columns to use fewer pages Location: http://www.mvps.org/dmcritchie/excel/snakecol.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Print __ pages wide by _ pages tall

Word allows you to put several pages of data onto a single page.
Excel does not have this facility but it does have a page scaling ability.

File, Page Setup, Page,
Scaling: [x] Fit to ____ pages(s) wide by ___ tall

Note those are two independent settings. You do not have to choose both. You can choose to print 1 page wide and print as many pages as needed. Also note use of Portrait or Landscape on the page tab.

Another page waster is being left with a lastcell (Ctrl+End) problem. The MakeLastCell macro can be useful in eliminating unwanted rows at bottom or columns to the right to change the lastcell.

Using MS Word to Snake Columns

This is probably the easiest to use for a one time usage, since I've not put in a dialog into my Excel macro described later.

Select and copy columns from Excel spreadsheet.
Paste into Word, this takes a few moments
Select the rows to be repeated at top in the table, then indicate this in Word with
Table --> Rows to repeated
Select entire table using anchor, or selecting cell(s) in the table and then
Table --> Select --> Table
Indicate number of rows using the columns button, or
Format --> Columns if you want more control over placement

PostScript Printers can print print 2-UP

PostScript printers can print multiple pages on a page, not what you would be looking for with a single column but it would save paper. Options on my printer during print are:

File --> Print --> [Properties] --> Document Options --> Page Layout (N-Up) Options --> 2-up

I have a choice of 1-Up, 2-UP (1x2), 4-UP (2x2), 6-UP (2x3), 9-UP(3x3), 16-Up(4x4)
underlining makes things hard to read in 16-UP at 300 dpi.

Attempting to Snake columns using Excel

Since snaking columns is so easily done with the help of MS Word and with a lot more control over printing that there is not much point to trying to do this in Excel -- at least as long as Excel itself does not support this feature. In fact the only reason to use Excel might be to keep down the proliferation of files where Word needs a separate file for each document, and Excel has separate documents as individual sheets.

Snakecols is incomplete (coded Jan 1999). As the macro currently stands you will have to modify the macro to specify some parameters such as number of rows on a page, how many heading lines, how many columns in the original. Macro coding can be found in snakecol.txt and you can experiment using snaketstcsv.txt test data.

The macro described here to effect snaking of columns, snaketstcsv.txt, is a VBA macro. If you need assistance to install or to use a macro please refer refer to my GetFormula page.

A B C

1 Col-A Col-B Col-C

2 A2 B2 C2

3 A3 B3 C3

4 A4 B4 C4

5 A5 B5 C5

6 A6 B6 C6

7 A7 B7 C7

8 A8 B8 C8

9 A9 B9 C9

10 A10 B10 C10

11 A11 B11 C11

12 A12 B12 C12

13 A13 B13 C13

'THIS WILL BE PUT INTO AN INPUTBOX LATER Hrows = 2 'specify number of heading rows cols = 2 'specify number of cols to copy setts = 4 'specify number of sets per page rowspp = 50 'specify number of rows per page

A B C D E F G H

1 Col-A Col-B Col-A Col-B Col-A Col-B Col-A Col-B

2 A2 B2 A2 B2 A2 B2 A2 B2

3 A3 B3 A53 B53 A103 B103 A153 B153

4 A4 B4 A54 B54 A104 B104 A154 B154

5 A5 B5 A55 B55 A105 B105 A155 B155

6 A6 B6 A56 B56 A106 B106 A156 B156

7 A7 B7 A57 B57 A107 B107 A157 B157

8 A8 B8 A58 B58 A108 B108 A158 B158

9 A9 B9 A59 B59 A109 B109 A159 B159

10 A10 B10 A60 B60 A110 B110 A160 B160

11 A11 B11 A61 B61 A111 B111 A161 B161

12 A12 B12 A62 B62 A112 B112 A162 B162

Creating a Table from something like a column of address labels

Simple example to take a single column, such as labels with Name; Street Address; City, State, Zipcode and convert to a table.

On a second sheet (or even same sheet), then use fill-handle
A1: =OFFSET('Contacts'!$A$1,Row()*3-3,0)
B1: =OFFSET('Contacts'!$A$1,Row()*3-2,0)
C1: =OFFSET('Contacts'!$A$1,Row()*3-1,0)
Another Solution, must be placed in cell B1 of same sheet #, then use fill-handle
B1: =INDIRECT("a"&ROW()*3-(4-COLUMN()))
Use the fill handle to copy down until you run out of contacts you can find the last cell on your Contacts sheet (ctrl+End) and divide the row by 3 to see how far down you will have to copy on your second sheet.
After you have completed this process your second sheet will be dependent on content and existence of your contacts sheet. To remove this dependency. Select all cells (ctrl+A) then Edit, paste special, values. You will now have constants without dependencies (no formulas).
Select row 1, Insert, rows; and then provide titles for your columns.

Before

A B

1 A1

2 A2

3 A3

4 A4

5 A5

6 A6

7 A7

8 A8

9 A9

10 A10

11 A11

12 A12

After

A B C D

1 A1 A2 A3

2 A4 A5 A6

3 A7 A8 A9

4 A10 A11 A12

5 A13 A14 A15

6 A16 A17 A18

7 A19 A20 A21

8 A22 A23 A24

9 A25 A26 A27

10 A28 A29 A30

11 A31 A32 A33

12 A34 A35 A36

To create a new worksheet from name and address label format using a macro see code for naddr3ss in my code directory. A variation that has 8 rows per set and each row has a descriptive title that would be used as the header for the column naddr8fss also seen in newsgroup as response

Something along the lines of a worksheet formula

This example only runs across but not down, but might give a better idea of why you would want a macro and secondly how you could do this with worksheet formulas and the fill handle. But any insertion or deletion of row would affect these formulas so you would have to use INDIRECT and of course we cannot process for more than a single row of displayed data with these formulas.

A B C D E F G H I J K

12 A12 B12 A24 B24 A36 B36 A48 B48 A60 B60 A72

13 =OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)

14 =OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)

15 =OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)

16 =OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)

17 =OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)

18 =OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)

19 =OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)

20 =OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)

Related Information - Snakecols

Test data, snaketstcsv.txt, was created using MarkCells(). Markcells is a useful utility because it allows you to create data that helps you see original location of cells before rearranging them with a program, or a macro such as snakecols. A technique I have used in programming and in Excel.
John Walkenbach has a utility named 'Transform Vertical Range' in his PUP 2000 add-in on his web site. (pay for)
Tom Ogilvy has posted formulas from time to time on snaking columns, thie formula is an interesting variation posted 2001-06-08, that will provide the combinations used in Columns A & B (use fill-handle).
=INDEX(A:A,INT((ROW()-1)/COUNTIF(B:B,"<>"))+1,1)&" "&OFFSET($B$1,MOD(ROW()-1,COUNTIF(B:B,"<>")),0)

You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on May 21, 1999.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com.

	A	B	C	D	E	F	G	H	I	J	K
12	A12	B12	A24	B24	A36	B36	A48	B48	A60	B60	A72
13	=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
14	=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)
15			=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
16			=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)
17					=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
18					=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)
19							=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
20							=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)