Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop sorting titles please
I have an excel document with 35000 rows and 125 columns of information. I
am sorting in almost every title column in multiple formats. is there a way to sort these with out always having to notify the sort I have headers? I want to click AtoZ or ZtoA and just go with out taking all the extra steps to manage the headers. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop sorting titles please
On Mar 11, 10:26*am, CC wrote:
I have an excel document with 35000 rows and 125 columns of information. *I am sorting in almost every title column in multiple formats. *is there a way to sort these with out always having to notify the sort I have headers? I want to click AtoZ or ZtoA and just go with out taking all the extra steps to manage the headers. Can you insert blank row in row2 please? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop sorting titles please
Suggestion - Here's my trick: Name the range you want to sort, excluding the
titles. Record a macro and create a macro button to perform the sort for you. I'm on Excel 2000 still, so if I tell you the exact steps I use it probably will be different for you. Now be careful - there's a potential problem: Adding rows at the top or bottom later on will likely result in those rows being outside the named range and being omitted from the sort. To prevent that happening, you can insert a blank row at the top and bottom of the range, reduce their height to very narrow and shade them with (say) grey (be sure to INCLUDE the shaded rows in the range when derfining the range name). You'll then always know to add any more rows BETEEN the shaded rows. Those shaded rows will never be used for your data, but by entering "AAA" or "ZZZ" in each cell - or just the sort keys - in those rows (and formatting their text colour to the same as the shading so its tidy and not visible) the shaded row will always sort to the beginning or end of your sort. Let me know if you need any more explanation, Cheers "CC" wrote: I have an excel document with 35000 rows and 125 columns of information. I am sorting in almost every title column in multiple formats. is there a way to sort these with out always having to notify the sort I have headers? I want to click AtoZ or ZtoA and just go with out taking all the extra steps to manage the headers. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop sorting titles please
I assume your headers are in Row 1, so yes, you'd make row 2 your blank row,
that is, blank until you type "AAA" into each sort key (or each cell). The "AAA" will ensure that the row always stays at the top if you are sorting alpha data. I should have made the point if you are sorting numerical data that you should instead of "AAA" use a numerical value that will be much larger (for descending sort) or smaller (for ascending sort) than anything you would be likely to be sorting. If you need to sum the columns, be sure to cancel those two rows out by contra-ing the positive value with the same negative value, thus eliminating the need to deduct the "dummy" values in the Sum formulae. EG: Column A Row 1 Header Row 2 999,999,999 (narrrow & shaded) Row 3-99 all your data Row 100 -999,999,999 (narrrow & shaded) Row 101 "=sum(A2:A100) Range Name, say, A2-Z100 = "SortArea" Save a copy of the file before you run the sort ;-) By the way, its not a bad idea, if you have any formulae that rely on testing for a zero value to limit the decimal places with a function such as "Round". Spreadsheets do funny things after about a zillion decimal places, but its enough to through a zero test off. Cheers "PY & Associates" wrote: On Mar 11, 10:26 am, CC wrote: I have an excel document with 35000 rows and 125 columns of information. I am sorting in almost every title column in multiple formats. is there a way to sort these with out always having to notify the sort I have headers? I want to click AtoZ or ZtoA and just go with out taking all the extra steps to manage the headers. Can you insert blank row in row2 please? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop numbering rows for Titles | Excel Worksheet Functions | |||
Sorting titles | Excel Discussion (Misc queries) | |||
Sorting titles for a mail out | New Users to Excel | |||
Sorting Titles | New Users to Excel | |||
How do I stop axis titles from overlapping with negative chart da. | Charts and Charting in Excel |