Need single column/row of numbers to print down then across pages in order

I have a very long list of phone numbers that occupies 
a1,a2, so on.  approx. 45,000 records.  I need to be able 
to print hard copies of these numbers so that it stays in 
order from page to page but is not just one column.  (as 
to cut the # of pages from 300 to 50 or lesss) In other 
words, start in the top left corner of page through to 
bottom, next row, and so on.  I thought there would be a 
way to set this up in printing options but to this point 
haven't figured it out.  I have the same list (1 row 
again) also in Access if it would be easier.  Would VERY 
much appreciate any clues.  Thanks.
0
anonymous (74722)
2/5/2004 8:03:53 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
599 Views

Similar Articles

[PageSpeed] 56

Curt

Either of these methods will "snake" the original column.  If you want some
other format, post back.

Manually............

If your data is an column A starting at Cell A1, then the following
formula, entered in Cell B1 and filled across 10 columns and down 4500
rows will produce your 10 columns of 4500 rows.  If you want more or less than
10 columns, you do the math and make alterations.

=INDIRECT("A"&(ROW()+(COLUMN()-2)*4500))

The 2 refers to the column of Cell B1; if you're putting the formula in
a different column, use the appropriate number for that column.

Copy>Paste Special(in place) the results then delete the original column A.

VBA Macro...............

Public Sub SplitToCols()
    Dim NUMCOLS As Integer
    Dim i As Integer
    Dim colsize As Long
    On Error GoTo fileerror

    NUMCOLS = InputBox("Choose Final Number of Columns")
    colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
            (NUMCOLS - 1)) / NUMCOLS)
    For i = 2 To NUMCOLS
        Cells((i - 1) * colsize + 1, 1).Resize(colsize, 1).Copy Cells(1, i)
    Next i
    Range(Cells(colsize + 1, 1), Cells(Rows.Count, 1)).Clear
fileerror:
End Sub

Gord Dibben Excel MVP


On Thu, 5 Feb 2004 12:03:53 -0800, "Curt"
<anonymous@discussions.microsoft.com> wrote:

>I have a very long list of phone numbers that occupies 
>a1,a2, so on.  approx. 45,000 records.  I need to be able 
>to print hard copies of these numbers so that it stays in 
>order from page to page but is not just one column.  (as 
>to cut the # of pages from 300 to 50 or lesss) In other 
>words, start in the top left corner of page through to 
>bottom, next row, and so on.  I thought there would be a 
>way to set this up in printing options but to this point 
>haven't figured it out.  I have the same list (1 row 
>again) also in Access if it would be easier.  Would VERY 
>much appreciate any clues.  Thanks.

0
Gord
2/5/2004 8:52:01 PM
Hi again.  Appreciate your help.  That almost does it but 
I wasn't too clear on exactly what I was looking for.  I 
need the hardcopy to be in numerical order page by page.  
In other words, I can't have 4500 records per row.  (Need 
to be able to reference numbers in order on hard copy) 
Only 60 max will fit per page.  Whew.  So basically, if it 
were just numbers in order starting at 1 I need 1-60 in 
column A, then 61-120 in column B until there are 10 
rows.. And from that point continue in order for each 
consecutive page that will be printed out. Only way I've 
found to do this is a painfully long slow process of 
copy/paste.  Sorry if I'm being a pain & I do appreciate 
any help.  Thank you....  Curt
>-----Original Message-----
>Curt
>
>Either of these methods will "snake" the original 
column.  If you want some
>other format, post back.
>
>Manually............
>
>If your data is an column A starting at Cell A1, then the 
following
>formula, entered in Cell B1 and filled across 10 columns 
and down 4500
>rows will produce your 10 columns of 4500 rows.  If you 
want more or less than
>10 columns, you do the math and make alterations.
>
>=INDIRECT("A"&(ROW()+(COLUMN()-2)*4500))
>
>The 2 refers to the column of Cell B1; if you're putting 
the formula in
>a different column, use the appropriate number for that 
column.
>
>Copy>Paste Special(in place) the results then delete the 
original column A.
>
>VBA Macro...............
>
>Public Sub SplitToCols()
>    Dim NUMCOLS As Integer
>    Dim i As Integer
>    Dim colsize As Long
>    On Error GoTo fileerror
>
>    NUMCOLS = InputBox("Choose Final Number of Columns")
>    colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
>            (NUMCOLS - 1)) / NUMCOLS)
>    For i = 2 To NUMCOLS
>        Cells((i - 1) * colsize + 1, 1).Resize(colsize, 
1).Copy Cells(1, i)
>    Next i
>    Range(Cells(colsize + 1, 1), Cells(Rows.Count, 
1)).Clear
>fileerror:
>End Sub
>
>Gord Dibben Excel MVP
>
>
>On Thu, 5 Feb 2004 12:03:53 -0800, "Curt"
><anonymous@discussions.microsoft.com> wrote:
>
>>I have a very long list of phone numbers that occupies 
>>a1,a2, so on.  approx. 45,000 records.  I need to be 
able 
>>to print hard copies of these numbers so that it stays 
in 
>>order from page to page but is not just one column.  (as 
>>to cut the # of pages from 300 to 50 or lesss) In other 
>>words, start in the top left corner of page through to 
>>bottom, next row, and so on.  I thought there would be a 
>>way to set this up in printing options but to this point 
>>haven't figured it out.  I have the same list (1 row 
>>again) also in Access if it would be easier.  Would VERY 
>>much appreciate any clues.  Thanks.
>
>.
>
0
anonymous (74722)
2/5/2004 11:51:33 PM
Curt

Try this one.  It is hard-coded to give you 10 columns in the format you want
with a blank row between each set of 60 rows.

I'm sure one of the brighter lights could revise to loop rather than hard-code
the cut and destination ranges.

    Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

    iSource = 1
    iTarget = 1

    Do
        Cells(iSource, "A").Resize(60, 1). _
        Cut Destination:=Cells(iTarget, "A")
        Cells(iSource + 60, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "B")
        Cells(iSource + 120, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "C")
        Cells(iSource + 180, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "D")
        Cells(iSource + 240, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "E")
        Cells(iSource + 300, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "F")
        Cells(iSource + 360, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "G")
        Cells(iSource + 420, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "H")
        Cells(iSource + 480, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "I")
        Cells(iSource + 540, "A").Resize(60, 1) _
        .Cut Destination:=Cells(iTarget, "J")
        
        iSource = iSource + 600
        iTarget = iTarget + 61
    Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub

Gord

On Thu, 5 Feb 2004 15:51:33 -0800, "Curt"
<anonymous@discussions.microsoft.com> wrote:

>Hi again.  Appreciate your help.  That almost does it but 
>I wasn't too clear on exactly what I was looking for.  I 
>need the hardcopy to be in numerical order page by page.  
>In other words, I can't have 4500 records per row.  (Need 
>to be able to reference numbers in order on hard copy) 
>Only 60 max will fit per page.  Whew.  So basically, if it 
>were just numbers in order starting at 1 I need 1-60 in 
>column A, then 61-120 in column B until there are 10 
>rows.. And from that point continue in order for each 
>consecutive page that will be printed out. Only way I've 
>found to do this is a painfully long slow process of 
>copy/paste.  Sorry if I'm being a pain & I do appreciate 
>any help.  Thank you....  Curt
>>-----Original Message-----
>>Curt
>>
>>Either of these methods will "snake" the original 
>column.  If you want some
>>other format, post back.
>>
>>Manually............
>>
>>If your data is an column A starting at Cell A1, then the 
>following
>>formula, entered in Cell B1 and filled across 10 columns 
>and down 4500
>>rows will produce your 10 columns of 4500 rows.  If you 
>want more or less than
>>10 columns, you do the math and make alterations.
>>
>>=INDIRECT("A"&(ROW()+(COLUMN()-2)*4500))
>>
>>The 2 refers to the column of Cell B1; if you're putting 
>the formula in
>>a different column, use the appropriate number for that 
>column.
>>
>>Copy>Paste Special(in place) the results then delete the 
>original column A.
>>
>>VBA Macro...............
>>
>>Public Sub SplitToCols()
>>    Dim NUMCOLS As Integer
>>    Dim i As Integer
>>    Dim colsize As Long
>>    On Error GoTo fileerror
>>
>>    NUMCOLS = InputBox("Choose Final Number of Columns")
>>    colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
>>            (NUMCOLS - 1)) / NUMCOLS)
>>    For i = 2 To NUMCOLS
>>        Cells((i - 1) * colsize + 1, 1).Resize(colsize, 
>1).Copy Cells(1, i)
>>    Next i
>>    Range(Cells(colsize + 1, 1), Cells(Rows.Count, 
>1)).Clear
>>fileerror:
>>End Sub
>>
>>Gord Dibben Excel MVP
>>
>>
>>On Thu, 5 Feb 2004 12:03:53 -0800, "Curt"
>><anonymous@discussions.microsoft.com> wrote:
>>
>>>I have a very long list of phone numbers that occupies 
>>>a1,a2, so on.  approx. 45,000 records.  I need to be 
>able 
>>>to print hard copies of these numbers so that it stays 
>in 
>>>order from page to page but is not just one column.  (as 
>>>to cut the # of pages from 300 to 50 or lesss) In other 
>>>words, start in the top left corner of page through to 
>>>bottom, next row, and so on.  I thought there would be a 
>>>way to set this up in printing options but to this point 
>>>haven't figured it out.  I have the same list (1 row 
>>>again) also in Access if it would be easier.  Would VERY 
>>>much appreciate any clues.  Thanks.
>>
>>.
>>

0
Gord
2/6/2004 12:53:58 AM
Reply:

Similar Artilces:

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

Need help getting files from Exchange Server.
We have a computer that had an account on Outlook attached to a Microsoft exchange server. Apparently for the moment, this computer and the server are inextricably tied together. Outlook cannot even be started without logging into the server because information store or whatever is missing. How do you get the information out of the exchange server to wean the computer from it? tom <Spamblocker@ameritech.net> wrote: > We have a computer that had an account on Outlook > attached to a Microsoft exchange server. > > Apparently for the moment, this computer and the > se...

Problem while transferring an Order to an Invoice
Hi, The problem occurred in the workstation; In the SOP screen an Order had been transferred to Invoice. In that situation the system got hanged. When the user logged out and re login again the specified Order had been in the transferred state, but there is no data in the Invoice screen against the transferred order. We can’t make an Order again, since the Item had been already transferred. Is that i need to look in backend tables and update the datas in the table level? Can anyone please suggest how we can able to recover the data for the transferred Order to the specified Invoice? ...

Sorting with Column has Formula
Hi everyone, I never imagined that the formula in the column would affect the sorting order in any way, but it does in my case. Below is the formula in that I have in Col I, and I'd like to sort it in Ascending order, but the result is that it sorts with all the empty rows on top and the one with the result from the formula at the bottom. I assume it consider the "I" in the "IF" function in the formula, but I'm not sure. Can anyone tell me how to fix this please? =IF(E2="","",IF(J2="X","Priority #1",IF(...

I need the fix for Knowledgebase ID: 810999
Where can I get this from? Thanks. In the last exciting episode, "Stephen Aldous" <steve@stephenaldous.com> wrote: >Where can I get this from? Other from Microsoft Product Support Services as stated in the article? No idea. -- Chris Scharff MessageOne http://www.messageone.com -- ROT13 for valid SMTP address -- Call Product Support Services as listed in the article. If all you're looking for is the hotfix, then the call should be free. -- David Sapery Exchange MVP dsapery@mvps.org "Stephen Aldous" <steve@stephenaldous.com> wrote in message...

numbering rows
Is there a way to number each row in Excel so that when it prints out the rows all have numbers? I couldn't find it in Msft online Help but I guess that's no surprise. thanks! Jane Two options. 1. In page setup>sheet select "row and column headers". Note this will print the column headsers also. 2. Insert a column to left of your original columns(will become column A) Enter in A1 =ROW() and drag/copy down the column as far as you wish. Gord Dibben Excel MVP On Fri, 23 Apr 2004 17:15:10 -0700, "jane" <jfnysf@hotmail.com> wrote: >Is there a ...

Permissions is not allowing me to use my print preview.
Help! Don't understand My Permission in Excel. Even though I unrestrict, it still doesn't allow me to Preview my documents. How do I reset? Please Help, ...

keeping page, insert etc dropdowns open & having page below it
how can i keep the drop downs open & have the page below it. It was that way & I must have hit the wrong button or something.microsoft word 2007 "billy" <billy@discussions.microsoft.com> wrote in message news:91198FA1-A43C-4A00-9141-FB019528AC5B@microsoft.com... > how can i keep the drop downs open & have the page below it. It was that > way > & I must have hit the wrong button or something.microsoft word 2007 Ask in a discussion group for Word. Here's how to find it. http://www.microsoft.com/communities/newsgroups/en-us/ In the...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

Varying column widths
I have 2 excel sheets - one is 20 columns wide the 2nd is 7 columns wide. The 20 column sheet has a general information section at the top that I would like to add to the 7 coulmn spreadsheet. However, when I copy and paste it "spreads out" or goes way beyond the width due to the number of columns. I have tried paste special object and entering the excel sheet that way and it doesn't fit properly and too many cells are shown. Any other way to do this? If you only want it up there for appearance purposes, you can try: Copy the selection. Click the cell where you want t...

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

Change Source Campaign on Invoice and Order
I need to change the Source Campaign on an Invoice and an Order but the field is locked. I tried to enable the field on the form but still cannot make changes. Any ideas beyond direct edit of DB? ...

Calling the Page Setup PopUp from VBA..
Hello.. I am using Access 2003 and I have a button on my form that I will have the user click to bring up the Page Setup popup screen just like you would do if you chose File -> Page Setup. Does anyone have the VBA code to accomplish this? Please advise... Cheers! Brett Try, docmd.RunCommand acCmdPageSetup Also, do not forget to trap error 2501 should the user cancel the operation. -- Hope this helps, Daniel Pineault "Brett Davis" wrote: > Hello.. > > I am using Access 2003 and I have a button on my form that I will have the > user click to br...

Printing in excel always comes out in bold even tho bold not on
When I print in excel my work always comes out in bold even though the bold is not switched on. Any suggestions?? It could be a printer setting. Does it print in bold from other applications? Regards, Fred "Scarlett50" <Scarlett50@discussions.microsoft.com> wrote in message news:DF3BAEA1-4D22-449E-BB8F-31DD8855AF20@microsoft.com... > When I print in excel my work always comes out in bold even though the > bold > is not switched on. Any suggestions?? No only when I have to type a report in excel - the actual report is ok on other computers! but...

Equivalent Column Break
In MSWord you can insert a column break when doing newspaper columns. If I have two text frames connected, and I am almost at the bottom of one but want Publisher to start at the top of the next one, how do I insert a 'break'? or do I have to press return until it move the text. Thanks Anita (1) You could shorten that column so that your text breaks where you want it to. (2) Or you could check the Help file and search for "insert break". Insert a column break You can insert a column break anywhere in a text box. If the text box contains more than one column, the ...

Printing in reverse order
I have printed out nearly 500 letters to be mailed out using mailmerge and publisher. I would like to not run them through the printer again and print address info on the reverse side so they can be run through a folding machine and mailed out. PROBLEM: the list and the pages are in reverse order and since I merged personal info on one side, the address side must correspond. Can publisher print out in reverse? I have tried copies from 500 - 1 but it still print from 1 - 500. The original list was not sorted in any manner, it was exported from a database. Suggestions please... TIA R...

Sort Need
A B C Row 1 206 S. Harbor Dr. 206 S. Harbor Dr. =LEFT(A1,FIND(" ",A1)-1) =TRIM(MID(A1,FIND(" ",A1)+1,255)) First equ immediately above is in B1 =LEFT(A1,FIND(" ",A1)-1) Second is in C1. =TRIM(MID(A1,FIND(" ",A1)+1,255)) A1 is address in column of addresses to be sorted. Equ starting: =LEFT pulls out the street address number Equ starting: =TRIM pulls balance. Problem: In this example, Sort places C1 in rows starting with l...

need help Combo Box with duplicate entry.
I have a combo box with unique and non-unique entries. (search field) 00010 | john | smith | 12345 | 00002 00196 | jane | doe | 0120 | 00001 00196 | Jone| wood| 0220 | 00005 I would like the following to happen. 1) user types the number needed ( 10 ) 2) the combo box zero fills the field (00010) 3) then selects an entry from the combo box. (12345) if the select is incorrect ( one of the non-unique numbers was selected - 00196) the user will open the combo box and select the correct entry. (jone wood) add the info will be put on the form. the following code works if the user ente...

Hiding Column and Row Bars.
I know theres a way to costumize the way you view an excel page by hiding toolbars, but is there a way to hide the rows and column bars just so all you can see is the actual page. E.G. is there a way to Hide the top bar that defines the colums "A", "B", "C","D" and Rows 1,2,3,4,5. Go to Tools>Options>View, there are a number of options you can play with there. -- HTH RP (remove nothere from the email address if mailing direct) "tamato43" <tamato43@discussions.microsoft.com> wrote in message news:7D1E5DEB-D6F2-47C8-95E2-2...

Column comparison
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Hi Dave, Need more information like a sample of the existing data plus a sample of what you want left. Maybe an explanation of the criteria for what needs to be deleted. -- Regards, OssieMac "Dave T" wrote: > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many than...

Disallowing duplication of nmbers in a column
Is Excel capable of disallowing the same numbers in a column? I have a column in a sheet that invoice numbers are entered into. I would like that column to alert or something if duplicate numbers are typed in. Hi Have a look here: http://www.cpearson.com/excel/NoDupEntry.htm -- Andy. "Barb1" <Barb1@discussions.microsoft.com> wrote in message news:E3631DD3-724E-4C0D-956B-4201876A9A4A@microsoft.com... > Is Excel capable of disallowing the same numbers in a column? I have a > column in a sheet that invoice numbers are entered into. I would like > that > c...

Need disk # 2 for Home Publishing Premium 2000
The #2 disk of a (6) disk set in my Home Publishing Premium 2000 was demolished in my CD drive. This is the setup disk and since the CD is no longer available from Microsoft I need a copy badly. Is there anyone that can successfully copy a #2 disk? I will be more than happy to pay anyone for there trouble. Patsy wrote: > The #2 disk of a (6) disk set in my Home Publishing > Premium 2000 was demolished in my CD drive. This is the > setup disk and since the CD is no longer available from > Microsoft I need a copy badly. Is there anyone that can > successfully copy a #2 dis...

automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...