SNAKECOLS,  How to snake columns to use fewer pages
Saving paper is not a feature of Excel.  Some tips and macros for snaking columns for smaller reports and to save paper. Some similar macros to rearrange columns / rows to prepare data for address labels from various sources including some with database tags.
 
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   (#pageswide)

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   (#msword)

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.
  1. Select and copy columns from Excel spreadsheet.
  2. Paste into Word, this takes a few moments
  3. Select the rows to be repeated at top in the table, then indicate this in Word with
      Table --> Rows to repeated
  4. Select entire table using anchor, or selecting cell(s) in the table and then
      Table --> Select --> Table
  5. 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   (#PS)

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.

Hiding intervening rows / columns   (#hiding)

You can hide the intervening rows / columns, and if the two areas line up you can possibly get them on the same page. If they line up horizontally you may have to print in landscape instead of portrait. Changing orientation (Landscape / Portrait) applies to the one spreadsheet only

Copy areas to another spreadsheet   (#copy)

Another possibility to save paper or to improve readability is to copy separate areas to another spreadsheet and print it.

Attempting to Snake columns using Excel   (#snakecols)

Several topics on this page contain or refers to some VBA macros.  If you need assistance to install or to use a macro please refer to my  «Getting Started with Macros« or delve into it deeper on my Install  page.

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 test data in snaketstcsv.txt test data.

The macro described here to effect snaking of columns is a VBA macro.  If you need assistance to install and use a macro please refer refer to my  Getting Started with Macros and User Defined Functions

 ABC
1Col-ACol-B  
2A2B2 
3A3B3 
4A4B4 
5A5B5 
6A6B6 
7A7B7 
8A8B8 
9A9B9 
10A10B10 
11A11B11 
12A12B12 
13A13B13 
14A14B14 
15A15B15 
   
Sub SnakeCols_test()
'Parameters for SnakeColsx 
'Hrows = 2     'specify number of heading rows
'Cols = 3      'specify number of cols to copy 
'setts = 4     'specify number of sets per page
'rowspp = 50   'specify number of rows per page
'ptsize = 0    'specify pointsize, must be > 6 to change
 Application.Run "personal.xls!SnakeColsx", 2, 3, 4, 50
End Sub        '[an extra column was included for spacing]

 ABCDEFGHIJK
1Col-ACol-B    Col-ACol-B    Col-ACol-B   Col-A Col-B
2A2B2  A2B2  A2B2 A2 B2
3A3B3  A53B53 A103B103  A153B153
4A4B4  A54B54 A104B104  A154B154
5A5B5  A55B55 A105B105  A155B155
6A6B6  A56B56 A106B106  A156B156
7A7B7  A57B57 A107B107  A157B157
8A8B8  A58B58 A108B108  A158B158
9A9B9  A59B59 A109B109  A159B159
10A10B10  A60B60 A110B110  A160B160

Creating a Table from something like a column of address labels   (#snkAddr)

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, then use fill-handle

for 3 lines per set start with:  (this is the example at right)
A1: =OFFSET(contacts!$A$1,ROW()*3-4+COLUMN(),0)&""

for 4 lines per set start with:
A1: =OFFSET(contacts!$A$1,ROW()*4-5+COLUMN(),0)&""

for 5 lines per set start with:
A1: =OFFSET(contacts!$A$1,ROW()*5-6+COLUMN(),0)&""

for 6 lines per set start with:
A1: =OFFSET(contacts!$A$1,ROW()*6-7+COLUMN(),0)&""

The concatenation of a null string at the end prevents a blank cell from producing a zero.

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

 AB
1A1 
2A2 
3A3 
4A4 
5A5 
6A6 
7A7 
8A8 
9A9 
10A10 
11A11 
12A12 
   After
 ABCD
1A1A2A3 
2A4A5A6 
3A7A8A9 
4A10A11A12  
5A13A14A15  
6A16A17A18  
7A19A20A21  
8A22A23A24  
9A25A26A27  
10A28A29A30  
11A31A32A33  
12A34A35A36  

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   (#WF)

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.

 ABCDEFGHIJK
12A12 B12A24B24A36B36 A48B48A60B60A72
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)

Reformatting 5 rows per set to 1 row (#reformat)

 
 ABC
 1 A1  B1-2 C1-3
 2 A2  B2-2  C2-3
 3 A3  B3-2  C3-3
 4 A4  B4-2  C4-3
 5 A5  B5-2  C5-3
 6 A6  B6-2 C6-3
 7 A7  B7-2  C7-3
 8 A8  B8-2  C8-3
 9 A9  B9-2  C9-3
10 A10  B10-2  C10-3

 

 ABCDEFG
 1 A1 A2  A3 A4 A5 B1-2 C1-3
 2 A6 A7  A8 A9 A10 B6-2 C6-3
 3 A11 A12  A13 A14 A15 B11-2 C11-3
 4 A16 A17  A18 A19 A20 B16-2 C16-3
 5 A21 A22  A23 A24 A25 B21-2 C21-3
Option Explicit
Public Sub N511_7()
'Convert 1-Up Name and Address labels to Spread Sheet format.
'David McRitchie,  2004-11-19,  programming
' http://www.mvps.org/dmcritchie/snake.htm#reformat
'      A1, A2, A3, A4,  A5, B1, C1   to 1st row
'      A6, A7, A8, A9, A10, B6, C6  to 2nd row,   etc.
Dim cRow As Long, newRows As Long, lastrow As Long
Dim wsSource As Worksheet
lastrow = Cells.SpecialCells(xlLastCell).Row
newRows = Int((lastrow + 4) / 5)
Set wsSource = ActiveSheet
Sheets.Add After:=Sheets(Sheets.Count)  '-- place at end
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For cRow = 1 To newRows
    'pick up cells from Column A
    Cells(cRow, 1) = wsSource.Cells(cRow * 5 - 4, 1)
    Cells(cRow, 2) = wsSource.Cells(cRow * 5 - 3, 1)
    Cells(cRow, 3) = wsSource.Cells(cRow * 5 - 2, 1)
    Cells(cRow, 4) = wsSource.Cells(cRow * 5 - 1, 1)
    Cells(cRow, 5) = wsSource.Cells(cRow * 5 - 0, 1)
    'additional cells from column B & C of each first row 
    Cells(cRow, 6) = wsSource.Cells(cRow * 5 - 4, 2)
    Cells(cRow, 7) = wsSource.Cells(cRow * 5 - 4, 3)
Next cRow
Cells.EntireColumn.AutoFit
Application.Calculation = xlCalculationAutomatic 
Application.ScreenUpdating = True
End Sub
The macro is suitable for converting a 1-up name and addressed label format to a name and address per row suitable for use in Mail Merge to print address or other labels.  There are additional examples of similar things on this (snakecol) web page.

Splitting up the First 3 columns to separate rows   (#splitting)

Original Sheet
 ABCDEFGHIJK
1A1  B1   C1  D1  E1  F1  G1   H1  I1  J1  K1  
2A2  B2   C2  D2  E2  F2  G2   H2  I2  J2  K2  
3A3  B3   C3  D3  E3  F3  G3   H3  I3  J3  K3  
4A4  B4   C4  D4  E4  F4  G4   H4  I4  J4  K4  
5A5  B5   C5  D5  E5  F5  G5   H5  I5  J5  K5  
6A6  B6   C6  D6  E6  F6  G6   H6  I6  J6  K6  
7A7  B7   C7  D7  E7  F7  G7   H7  I7  J7  K7  

 

New Sheet Formulas:
A1:  =OFFSET(OrigSheet!$A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))
 
B2:  =OFFSET(OrigSheet!D$1,INT((ROW()-1)/3),0)
  New Sheet
 ABCDEFGHI
1A1 D1 E1  F1 G1 H1 I1 J1 K1 
2B1 D1 E1  F1 G1 H1 I1 J1 K1 
3C1 D1 E1  F1 G1 H1 I1 J1 K1 
4A2 D2 E2  F2 G2 H2 I2 J2 K2 
5B2 D2 E2  F2 G2 H2 I2 J2 K2 
6C2 D2 E2  F2 G2 H2 I2 J2 K2 
7A3 D3 E3  F3 G3 H3 I3 J3 K3 
8B3 D3 E3  F3 G3 H3 I3 J3 K3 
9C3 D3 E3  F3 G3 H3 I3 J3 K3 
10A4 D4 E4  F4 G4 H4 I4 J4 K4 
11B4 D4 E4  F4 G4 H4 I4 J4 K4 
12C4 D4 E4  F4 G4 H4 I4 J4 K4 
13A5 D5 E5  F5 G5 H5 I5 J5 K5 
14B5 D5 E5  F5 G5 H5 I5 J5 K5 

Joining Codes in Column B to a Product in Column A   (#joining)

 AB
111111112
211111113
311111113
422222211
533333312
633333312
 
 AB
111111112, 13, 13
222222211
333333312,12
  JOINCODE macro creates a new sheet.   Macro joins Column B
to a Product in Column A.  Coding can be found at http://www.mvps.org/dmcritchie/excel/code/joincode.txt

A variation (Dave Peterson 2004-11-06) with 5 colums to check for
equality then combine the unique 6th column.

Clear Duplicate A and B column cells   (#cleardupAandB)

Sub cleardupAandB()
  'D.McRitchie, 2004-08-11 snakecol.htm
  Dim lastrow As Long, i As Long
  lastrow = Cells(Cells.Rows.Count, _
     "A").End(xlUp).Row
  For i = lastrow To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) _
      And Cells(i, 2) = Cells(i - 1, 2) Then
        Cells(i, 1) = ""
        Cells(i, 2) = ""
    End If
  Next i
End Sub
'more complete code at code/snakecol.txt
   
A1 B1C1D1E1
A1B1C2D2E2
A1B1C3D3E3
A4 B4C4D4E4
A4B4C5D5E5
A4B4C6D6E6
A4B4C7D7E7
A8 B8C8D8E8
A8B8C9D9E9
   
A1 B1C1D1E1
------C2D2E2
------C3D3E3
A4 B4C4D4E4
------C5D5E5
------C6D6E6
------C7D7E7
A8 B8C8D8E8
------C9D9E9

For a more generic solution see Fill in the Empty Cells, this macro will fill in empty cells with the content of the cell above it, providing the cell above is also within the selection range.  Also a macro to clear cell if a duplicate of the cell above.

Text to Columns, Split at LF (line=feed character) (#lf)

If you used Ctrl+Alt to place like 4 lines of data into a cell and now discover you need to have them all on one row for a Mail Merge, you can use Text to Columns to spread the cell content to the columns to right.
  
 A
 1  John Doe
 111 Smith Drive
 Two Horses, Utah  
   UT 12344-6789
 

Split Cells with LF in Column A (and B) into new rows   (#split10_to_rows)

Spit cells with LF Char(10) into new rows based on content in Column A, will split B as well if there is a cell to be broken apart in Column A. [macro]

Creating a Spreadsheet from Database data   (#dbdata)

'Convert 1-Up Name and Address labels to Spread Sheet format.
'David McRitchie  http://www.mvps.org/dmcritchie/excel/code/naddrdb.txt
'   2002-05-05 NAddrDB macro work with names as arg in A and value in B
'    will accept Arguments in any order within blank row delimited ranges
 AB
1NameRogers
2Dob19/06/54
3Address54 High St
4Tel218937
5Fax2187398
6Emailjames@mail
7  
8NameSmith
9Dob05/02/69
10Tel7557254
11Fax454566
12Address21 New Road
 
 ABCDEF
1NameDobAddress TelFaxEmail
2Rogers19/06/54 54 High St2189372187398james@mail
3Smith5/2/1969 21 New Road7557254454566 
Obtain NADDRDB macro and install.
Instructions to install a macro can be found on my getstarted.htm page.  For even more information on macro see Install Macros and User Defined Functions, which used to be part of my Dana DeLouis provides another solution in the same thread.  Timings are about the same when including turning off calculations and screen updating.

A variation of this was that there were several address lines all with the same tag ("infos") and all were to be included separately.  See reply post 2003-07-22 modification based on NaddrDB.

An incomplete CSV file within Column A (#PopulateAddr3Data)

Data in Column A looks like this
FirstName, Last Name, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2, Addr3, City, ST Zip, Country

Should have looked like this
FirstName, Last Name,,,, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2,, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2, Addr3, City, ST Zip, Country

A macro to add the missing commas and then invoke Text to columns with a comma delimiter and not ignore duplicate delimiters can be found in my posted reply 2004-08-29, programming.

Unsnake Columns   (#unsnake)

Although this was probably covered earlier, here ae some simple examples to unsnake a worksheet from another worksheet with Worksheet Formulas.  Can also be used on same worksheet if you want to see original and results.

To unsnake Column A and B into one column (source: A1,B1, A2,B2, A3,B3, etc.
  =OFFSET(OrigSheet!$A$1,INT((ROW()-1)/2),MOD(ROW()-1,2))

InsertRowsAndFillFormulas(Optional vRows As Long)

Simplify data for Pivot Table Use (#simplify)

 ABCDEF
 1  A1  B1  C1 D1-1  E1-2 F1-3
 2  A2  B2  C2 D2-1 E2-2  F2-3
 3  A3  B3  C3  D3-1  E3-2 
 4  A4  B4  C4  D4-1   F4-3
 5  A5  B5  C5 1   100  1,000 
 6  A6  B6  C6 D6-1 E6-2  F6-3

 
 ABCD
 1  A1  B1  C1 D1-1
 2  A1  B1  C1 E1-2
 3  A1  B1  C1 F1-3
 4  A2  B2  C2 D2-1
 5  A2  B2  C2 E2-2
 6  A2  B2  C2 F2-3
 7  A3  B3  C3  D3-1
 8  A3  B3  C3  E3-2
 9  A4  B4  C4  D4-1
10  A4  B4  C4  F4-3
11  A5  B5  C5 1 
12  A5  B5  C5 100 
13  A5  B5  C5 1,000 
14  A6  B6  C6 D6-1
15  A6  B6  C6 E6-2
16  A6  B6  C6 F6-3
Sub Split_2_splits()
  'David McRitchie, 2006-03-28, snakecol.htm 
  Dim stub_columns As Long, arg_columns As Long
  Dim lastrow As Long
  stub_columns = 3  'columns A:C
  arg_columns = 3   'columns D for 3 columns
  Dim oldSht As Worksheet, newSht As Worksheet
  Dim r As Long, c As Long, nr As Long, ac As Long
  Dim ac_from As Long, ac_to As Long
  ac_from = stub_columns + 1
  ac_to = stub_columns + arg_columns
  lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  nr = 0
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Set oldSht = ActiveSheet
  Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("new_work").Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name _
     = "new_work"
  Set newSht = ActiveSheet
  For r = 1 To lastrow
    For ac = ac_from To ac_to
      If Trim(oldSht.Cells(r, ac)) <> "" Then
         nr = nr + 1
         For c = 1 To stub_columns
            newSht.Cells(nr, c) = oldSht.Cells(r, c)
            newSht.Cells(nr, c).Select
            oldSht.Cells(r, c).Copy
            newSht.Cells(nr, c).PasteSpecial _
              Paste:=xlPasteFormats, Operation:=xlNone, _
              SkipBlanks:=False, Transpose:=False
         Next c
         newSht.Cells(nr, ac_from) = oldSht.Cells(r, ac)
         newSht.Cells(nr, ac_from).Select
         oldSht.Cells(r, ac).Copy
         newSht.Cells(nr, ac_from).PasteSpecial Paste:=xlPasteFormats, _
            Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False        
      End If
    Next ac
  Next r
  newSht.Activate
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub
It should be rather apparent that Excel does not do a good job at printing name and address labels; whereas, Microsoft Word does have adequate label printing facilities.  Microsoft expects you to use Word to print labels and to use Excel as the database.  For a comprehensive treatment of printing labels with Word on my site see Mail Merge, some notes on Mail Merge and Excel

Related Information - Rearranging Data within same row


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. 

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

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2006,  F. David McRitchie,  All Rights Reserved