Quick Excel Question

[GirlySports]

I have an excel document that I would like to print. I only need to print one column, but the column spans 85 pages. Is there a way to make excel put multiple sets of the same column on one page to reduce the number of pages it prints? Or am I going to have to cut and paste a crapload?

[Fobulous]

Not sure if this is what you are looking for, but highlight the column, click 'File' -> 'Print Area' -> 'Set Print Area'.

This will now only print the sections you highligted.

[sec304]

yeah, I'd open a new tab and cut and paste your one column into many, then set the print area

[GirlySports]

Originally Posted by Fobulous

Not sure if this is what you are looking for, but highlight the column, click 'File' -> 'Print Area' -> 'Set Print Area'.

This will now only print the sections you highligted.

I want it on as few pages as possible.

[Fobulous]

I am sure there is an easier way, but maybe copy the whole column, paste it into word, and then use the columns option to fit as many columns onto one page.

[Ducay]

Select the cells to be printed

Then set print area (file)

Then go to page setup and tell it how many pages you want it to print onto (so if you want it all on 1 or 2 or 120 pages).

[Ashartus]

Originally Posted by Ducay

Select the cells to be printed

Then set print area (file)

Then go to page setup and tell it how many pages you want it to print onto (so if you want it all on 1 or 2 or 120 pages).

I don't think that would work - you still end up with just one column, scaled really small to fit within the specified pages. I'm not actually sure if there is a way to have a single column print as multiple columns across the page within Excel, short of cutting and pasting to re-arrange the spreadsheet.

[Fobulous]

Just tried doing it the way I posted above...

Copy and paste the entire column into word, then click 'format' ->
'Columns' -> Select the number of columns you like and click ok.

That should put them all on one page.

[Frequitude]

Originally Posted by Ducay

Select the cells to be printed

Then set print area (file)

Then go to page setup and tell it how many pages you want it to print onto (so if you want it all on 1 or 2 or 120 pages).

Then it'll be ridiculously tiny. She wants lots of normal sized columns side by side on the page.

I don't think there's a way to do this from the print functions. You actually have to manipulate the data into lots of side by side columns. So either cut and paste, or set up a set reference formulas using the OFFSET function.


Edit: SHE!

[Frequitude]

Like this:




1) Data on the left

2) Set up the a row and column of reference numbers like I did along the top and left for how many datapoints you want in each column. (edit: Note that the column starts at 0 and goes to how many cells per column you want minus 1. Then the row at the top starts at zero and goes up in multiples of how many cells per column you want. So if you wanted each column to have, say, 35 cells you'd make the reference on the left go from 0 to 34, and the reference across the top go 0, 35, 70, 105, etc.)

3) Follow the offset formula I showed, noting the direct/indirect referencing.

[GirlySports]

Originally Posted by Fobulous

I am sure there is an easier way, but maybe copy the whole column, paste it into word, and then use the columns option to fit as many columns onto one page.

This worked quite well. Good enough for what I need.
Thanks

/I'd still like to know if there's an excel function so I could save the file like that.

[Frequitude]

If you do the indirect/direct referencing right like in the screenshot above, just copy and paste that formula throughout the box you set up and you'll get this:

[GirlySports]

grrr it's 8500 rows

[Frequitude]

It'll take 2 minutes!

Do, say 50 cells per column. So 0-49 on the left, 0-50-100-...-8450 on the top. Copy, paste, done.

Edit: the 0-50-100-...-8450 part can be done easily by just doing the cell to the left plus 50, and copying that formula to the right. Sorry if you knew that, not sure how comfortable in excel you are.

[Frequitude]

You can email it to me and I'll do it for you...but try to figure it out first. Can't beat learning new excel tricks.

[GirlySports]

woo, it worked! thanks

[Ducay]

Originally Posted by Ashartus

I don't think that would work - you still end up with just one column, scaled really small to fit within the specified pages. I'm not actually sure if there is a way to have a single column print as multiple columns across the page within Excel, short of cutting and pasting to re-arrange the spreadsheet.

Ya, didn't really read the OP carefully enough, thought she just wanted to print one column

[Frequitude]

Originally Posted by GirlySports

woo, it worked! thanks

If you would just be so kind as to fix my leaky toilet we'll call it even.

[GirlySports]

Originally Posted by Frequitude

If you would just be so kind as to fix my leaky toilet we'll call it even.

I'm scared of toilets

[Mad Mel]

Or you could just run a VBA macro.

Public Sub TransposeColumnToMultipleColumns()

    MaximumNewRows = 50         'Maximum number of rows in output
    NewColumn = 3               'First column used in output
    
    'Loop through rows of first column
    For Each RowObj In ActiveSheet.Rows
    
        'Exit when it hits a blank cell
        CellValue = RowObj.Cells(1, 1)
        If CellValue = "" Then Exit For
        
        'Put the value into it's new location
        NewRow = RowObj.Row Mod MaximumNewRows
        If NewRow = 0 Then NewRow = MaximumNewRows
        ActiveSheet.Cells(NewRow, NewColumn) = CellValue
        
        'Move to the next column when the maximum number of rows is reached
        If NewRow = MaximumNewRows Then NewColumn = NewColumn + 1
                
    Next RowObj
    
End Sub