Making an alphabetical list that fits on one page.

Hello,

I know how to arrange a column of cells into alphabetical order when 
producing a list. However, how can I fit the list across columns so that it 
fits on one printable page?

At the moment, I cut the parts of the list that enters onto the next pages 
and fit them into the next column. The trouble is that when making new 
entries I have to make sure that the list is put back into one column again 
so that it will automatically sort them alphabetically. This is a pain! 
There must be an easier way.

Thanks for your attention and any advice you can give me.

David 


0
10/8/2005 9:10:18 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
479 Views

Similar Articles

[PageSpeed] 21

David

How long is your list(how many rows)?

How do you want them laid out on the printed pages?

Snaked like 1-50 in column A and 51-100 in column B and 101 -150 in column C
or snaked in alphabetical order down A then up to top of B then down then up
to top of column C etc.?

How many columns do you want to end up with?

Which macro to post will depend upon some answers to these questions.


Gord Dibben Excel MVP

On Sat, 8 Oct 2005 22:10:18 +0100, "David" <thebluecarrot@hotmail.com> wrote:

>Hello,
>
>I know how to arrange a column of cells into alphabetical order when 
>producing a list. However, how can I fit the list across columns so that it 
>fits on one printable page?
>
>At the moment, I cut the parts of the list that enters onto the next pages 
>and fit them into the next column. The trouble is that when making new 
>entries I have to make sure that the list is put back into one column again 
>so that it will automatically sort them alphabetically. This is a pain! 
>There must be an easier way.
>
>Thanks for your attention and any advice you can give me.
>
>David 
>

0
Gord
10/8/2005 9:33:25 PM
Thanks for the reply,

Basically I want to compile a list of my DVD collection. Therefore it will 
consist of one cell per DVD that will be in column A and will stretch down 
to about 300 cells.

Obviously depending on the font size, I would like to be able to fit the 
list 4 or 5 columns across and so decreasing the stretch of 300 to just 60 
cells going down, but be able to add to the list where it will automatically 
fit the new entry in alphabetically.



"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:37egk19l13hdc3p3ah1d5tig8hq65fsp6j@4ax.com...
> David
>
> How long is your list(how many rows)?
>
> How do you want them laid out on the printed pages?
>
> Snaked like 1-50 in column A and 51-100 in column B and 101 -150 in column 
> C
> or snaked in alphabetical order down A then up to top of B then down then 
> up
> to top of column C etc.?
>
> How many columns do you want to end up with?
>
> Which macro to post will depend upon some answers to these questions.
>
>
> Gord Dibben Excel MVP
>
> On Sat, 8 Oct 2005 22:10:18 +0100, "David" <thebluecarrot@hotmail.com> 
> wrote:
>
>>Hello,
>>
>>I know how to arrange a column of cells into alphabetical order when
>>producing a list. However, how can I fit the list across columns so that 
>>it
>>fits on one printable page?
>>
>>At the moment, I cut the parts of the list that enters onto the next pages
>>and fit them into the next column. The trouble is that when making new
>>entries I have to make sure that the list is put back into one column 
>>again
>>so that it will automatically sort them alphabetically. This is a pain!
>>There must be an easier way.
>>
>>Thanks for your attention and any advice you can give me.
>>
>>David
>>
> 


0
10/8/2005 10:05:48 PM
David

I would have two sheets, one for the entry and one for printing.

After adding new entries to the original sheet, run this macro to copy the
column to a new sheet, sort it then split into a user's choice of number of
columns.

Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
    On Error GoTo fileerror
    Sheets("Original") _  '"Original" will be your sheet name
    .Columns(1).Copy Destination:=Worksheets.Add.Range("A1")
    With Columns("A:A")
    .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End With
    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



On Sat, 8 Oct 2005 23:05:48 +0100, "David" <thebluecarrot@hotmail.com> wrote:

>Thanks for the reply,
>
>Basically I want to compile a list of my DVD collection. Therefore it will 
>consist of one cell per DVD that will be in column A and will stretch down 
>to about 300 cells.
>
>Obviously depending on the font size, I would like to be able to fit the 
>list 4 or 5 columns across and so decreasing the stretch of 300 to just 60 
>cells going down, but be able to add to the list where it will automatically 
>fit the new entry in alphabetically.
>
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:37egk19l13hdc3p3ah1d5tig8hq65fsp6j@4ax.com...
>> David
>>
>> How long is your list(how many rows)?
>>
>> How do you want them laid out on the printed pages?
>>
>> Snaked like 1-50 in column A and 51-100 in column B and 101 -150 in column 
>> C
>> or snaked in alphabetical order down A then up to top of B then down then 
>> up
>> to top of column C etc.?
>>
>> How many columns do you want to end up with?
>>
>> Which macro to post will depend upon some answers to these questions.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Sat, 8 Oct 2005 22:10:18 +0100, "David" <thebluecarrot@hotmail.com> 
>> wrote:
>>
>>>Hello,
>>>
>>>I know how to arrange a column of cells into alphabetical order when
>>>producing a list. However, how can I fit the list across columns so that 
>>>it
>>>fits on one printable page?
>>>
>>>At the moment, I cut the parts of the list that enters onto the next pages
>>>and fit them into the next column. The trouble is that when making new
>>>entries I have to make sure that the list is put back into one column 
>>>again
>>>so that it will automatically sort them alphabetically. This is a pain!
>>>There must be an easier way.
>>>
>>>Thanks for your attention and any advice you can give me.
>>>
>>>David
>>>
>> 
>

0
Gord
10/8/2005 11:03:55 PM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

multiple flyers per page?
Hello. I need to make a custom template with publisher. It will be one 8 1/2 by 11 paper divided horizontally into three parts so that there are three fliers that can be printed. each flier needs to be double printed so I need to make it a double sided project like a postcard or brochure layout. I can figure out text and image stuff. But I don't know how to make a basic custom project that is divided into three panels horizontally (so 8 inches by 3.83 inches roughly) Also an additional customization would divide each 3.83 by 8 inch flyer into two parts so one 8 by 11 page would ...

printing 2 charts on single page
Using Excel 2k, Win 2k I was trying to print to line charts on a single page to demonstrate the corelation between the two data sets. I would like to print in portait mode with one chart on top of page and one on bottom. Excel only seems to offer putting chart 2 on a separate page or embedding it in chart one. I am not very familiar with excel charting capabilites can anyone suggest a resorce? Thanks ...

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Page setup for 8.5x11 calendar w/picture on tabloid paper?
How can I set up a calendar with 8.5x11 (landscape) pages with pictures for each month to print on tabloid (11x17) paper so it can be saddle stiched? I would like the picture on the top half and calendar on the bottom half. I keep ending up with two of the same pictures on one page and two calendars on the next page. Thanks, Bob I assume you are taking this to a professional printer so why not just set each page up as a landscape 8� x 11 and let your printer do the rest? -- JoAnn Paules MVP Microsoft [Publisher] "Bob at 4799" <Bob at 4799@discussions.microsoft.com>...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Cant read one instance of recurring appointment
The problem we're having is a little different that what I've seen posted. A user randomly gets the message "Cant read one instance of recurring appointment. close any open appointments and try again, or recreate the appointment" several times a day. When you click OK the same message appears several times. Outlook doesn't indicate which recurring appointment is having the problem and the message just pops up at various times even if the user isn't in the calendar. We've deleted the Frmcache file and run the clean profile switch, but that hasn't worked. Has an...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Exchange 2003, Two Sites, Two Smarthosts, One Domain, No Internal mail!!
Hi, I hope there is someone out there who can point me in the right direction. We have two sites, Brisbane and Sydney, connected by a frame realy connection. Sydney is currently running exchange 2003 (upgraded by migration from exchange 5.5 about a month ago). Brisbane is currently running their mail through sendmail and sendmail only. We have just installed exchange 2003 on server 2003 box in Brisbane and are trying to get the two sites working in two routing groups. The topology will be the same in both Brisbane and Sydney, in that each city will have an external mail server running sendma...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where $G$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

distibution list / updating
when we update our contacts how can we get outlook to update our distribution lists at the same time. What do you mean exactly? Can you give an example? Also what version of Outlook are you using? T. >-----Original Message----- >when we update our contacts how can we get outlook to >update our distribution lists at the same time. >. > That's what the Update Now button is for. -- Russ Valentine [MVP-Outlook] "wayne" <wayne@anandasacramento.org> wrote in message news:05ba01c3d4be$e83a5e80$a301280a@phx.gbl... > when we update our contacts how can we g...

Print a list of movies
I have a spreadsheet with 2 columns (No and Name of Movie). There are 322 rows. I would like to print this so that I have the first 44 rows on the left side of the page and the next 44 rows on the right side of the page. I would like to keep this setup on all pages. These cells have color formatting and numbers that I need to keep with each cell. How do I do this? -- Terri Sprague www.tlsprague.com Hi Teri, See http://www.mvps.org/dmcritchie/excel/snakecol.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/...

Setting up Word to automatically open in Standard mode, zoom=page margins
I've tried to find ways to handle this through the normal.dot file and can't find it... Word wants to open in layout mode and 200% zoom, forcing me to manually select mode and zoom. Any way to get Word to do what I want instead of what it wants? Thanks for your time. ===== Edward Lipsett Fukuoka, Japan Hi Edward: Word will open each document in the view and zoom it as last saved in. The onyl way to have a particular view happen every time is to create an AutoOpen() macro that specifies the view you want. Turn on your Macro Recorder and record yourself setting the view and zo...

How to export users list from MS-Exchange AD Users&Computers?
Hello, I want to export my exchange mailbox-enabled users list with their Given Name+Surname and e-mail address.. I get this kind of list from ActiveDirectory Users&Computers but I can not export it.. I know that there is a program somewhere that does what I want, but I can't remember it's name.. Note: I don't mean LDIFDE tools.. Thanks for your help. Capricorn As always...be careful and test things in a lab first, please. LDIFDE tools can be pretty ugly... CSVDE will work and it is possible to limit the columns that are output by CSVDE. Note: If you have a large ...

Making month boxes in Calendar transparent
I have been trying to create a calender where I use my own background. However, the boxes with the month information block most of the background because they are not transparent. Is there a way to change this so that the background is visible through them? Thanks for any help. Calendars are tables, select the cells, select fill, click *no fill* -- Mary Sauer http://msauer.mvps.org/ "Kat Lomas" <Kat Lomas@discussions.microsoft.com> wrote in message news:DB63402F-D428-4F76-BE91-029CF4EEA8CC@microsoft.com... >I have been trying to create a calender where I...

Inventory list columns
There needs to be more columns avaialble in the inventory and other lists. It would also be nice to be able to print from the inventory lists. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en...

return max date from list
I thought this should be easy enough, but it's been years since I've done serious excel work and I have gone into brainlock trying to figure this out.. What I want to do is, find the date of the last occurance of a value, in this case a number. For example: A B C D E 1 23 32 44 01-JUL-03 2 3 7 56 03-AUG-04 3 12 34 45 23-OCT-05 4 23 26 58 24-MAR-06 5 15 37 79 23-JUN-06 6 11 32 39 28-DEC-06 I have a list of all possible numbers in another location, 1 thru 200, and I want to put a formula beside each number that will find the last date that number occurred in the array, in any ...

check List Cell
check List Cell I woul like a cell with the symbol of check for create a checking list in excel. how can i do and format a cell ? I would like to get a template with this options and add my list. I only need to select o deselect the items. Thanksxxx -- Esgrimidor hi if i understand correctly, format the cell to font marlett the type the lower case a. a check mark will appear. Regards FSt1 "Esgrimidor" wrote: > > check List Cell > > I woul like a cell with the symbol of check for create a checking list > in excel. > > how can i do and format a...