« June 2011 | Main | August 2011 »

It's all downhill from here

Riding Red Tricycle IMG_3870photo © 2009 Steven Depolo | more info (via: Wylio)Lately I've been doing a lot of reading about (and shopping for) new kinds of wheeled vehicles to ride. I haven't made up my mind yet about what to ride. There's just too many choices; maybe another time I will blog about new bike tech. But though I don't yet know what to ride, I do know where to ride thanks to the USGS Map Locator.

Don't let their "store" name fool you, lots of USGS stuff is available for free. I went online searching for topographic maps, expecting to be able to buy some fresh paper topo quads like I sometimes take on camping or canoe trips. They still have those, but even better they have downloadable PDF topo quads with seperable layers, for free!

The separable layers make this the most awesome map product I've ever owned. Like magic I can just peel away things and put them back for different views of the terrain and/or the human infrastructure on it. The best part is that the bottom layer isn't pen and ink, it's an "orthoimage" made of a mosaic of aerial photos (satellite and/or plane). That means I can easily tell not only where a riding route will be flat or hilly, but where it's likely to be shady or sunny as well.

These PDF maps are kind of like a pocket Google Earth, but without needing a browser plugin or even needing to be online at all. With a tablet or smart phone, you can take the PDF with you and use it even in back country areas with no reception (though I'd like a paper copy too in those conditions). Now to find a commuting route that is downhill in both directions...

Excel 2007: How to sort data in a spreadsheet

Ever wanted to sort a subset of data in a “Long in Transit" report or some other big spreadsheet?  Not including printing, it's a four-step process.E_icon

Backup the original spreadsheet:
Before sorting, be sure to make a backup copy of the original spreadsheet.  That way if things go astray, you have an easy way out.

Freeze the column headers:
If the spreadsheet spans more than a single screen, you may want to ‘freeze’ the column headers before you begin.  That way as you scroll down the spreadsheet, the column headers will be preserved.

  1. Select the View tab from the ribbon.
  2. In the Window subsection, click the down arrow associated with Freeze Panes.
  3. Select Freeze Top Row.
  4. A black bar should appear beneath the first row in the worksheet.  This will indicate that the area above the bar has been 'frozen'.
  5. 

An example sort:
For this example, let’s sort the Date sent column associated with POR’s data in a ‘Long in transit…’ report (for POR’s data only).


Highlight all of POR’s data:
This will involve a Shift-Click keyboard-mouse sequence.

  1. Visually determine the 1st (in this example: 1,089) and last rows (in this example: 1,122) that contain POR data.First Row
  2. Click the mouse in the first cell of POR’s data, A1089.
  3. Depress the left Shift key and leave it depressed.
  4. Depress the left mouse button and while keeping it depressed, drag it to the end of the row, column ‘J’.
  5. At this point, all the data between A1089 and J1089 should be hightlighted.
  6. Still with the Shift key and the left mouse button depressed, scroll down the spreadsheet until row J1122 is highlighted (the last cell of the POR data).Last Row
  7. Release both the Shift key and the left mouse button.

Sort POR’s ‘Date Sent’ column:

  1. Click on the Data tab on the Ribbon.
  2. In the Sort and Filter subsection, click the Sort option.
  3. In the Sort window:
  • Remove the check mark from My data has headers.
  • Click the down arrow associated with Sort by and select Column ‘H’ (the 'Date Sent’ column).
  • Click the OK button. Your data will now be sorted. Sort by Date Sent

Print the sorted results and fit it on one page:
There’s a really easy way to print out only the subset of data that you just sorted.

  1. Click the Page Layout tab on the Ribbon.
  2. In the Page Setup subsection, select Print Area.
  3. Select Set Print Area.  This will print what’s been highlighted previously.
  4. Also, in the Page Setup Area, select Print Titles. Click the Page tab and then click the radio button for Fit to under the Scaling section.  This will compress all the data onto one page when printing.
  5. Click OK.
  6. Print you sorted data by whatever mechanism you normally print.

FYI: Excel is capable of sorting more than one column of data at a time.