the Wasatch Range Climbs By Rating spreadsheet

I am trying to compile all of the climbs along the wasatch (basically what is in Stuart Ruckman guide + AF) in a comprehensive spreadsheet that is organized by rating. The pic shows an example. There will be seperate sheets for each area and/or one main sheet with all of the areas. The idea is a personal ticklist, so If I want to work all of the 11c's it will be super easy to keep track of, and will help me not end up at the same crags week after week. Of course as an excel spreadsheet you can sort and filter or print it out and keep it with your book.

As of now I have everything off of MP.com (for the areas seen in the spreadsheet tabs) and 95% organized. I need to pull the stuff out of the back of the SR book. I can scan and use adobe to convert the images to text and insert into spreadsheet, but I have already gone and marked up my book so I can't get a clean scan. Can anyone with a clean index PLEASE scan it and shoot it my way via email: tyler at directinet dot com? I am happy to make this spreadsheet available to anyone who is interested.

And of course, Rock Canyon gets left out :)

Mine is clean. Do you want the alphabetical index or the index by rating?

Ha! Well I avoid travel in that valley! ;) This guide has no route info, it relies on route info in other books / MP.com. I'll leave the secrets to you! Unless you actually do want it in there, in which case we can talk.

I need the index by rating section. It would be much appreciated!

this sounds like a great idea. I would definitely be interested in getting a copy of the final product, but i'm sorry to say my guide is sitting many miles away from me in Utah.

Scanned and emailed. Let me know if there's something else you need.

I'd like a copy of the final product. If you don't mind.

Good hell Tyler, get a job. Or something.

I'd love a copy as well please.
Thanks

zoso wrote:Good hell Tyler, get a job. Or something.

Hahaha!!! Coming from the guide book whore! Don't worry BP you'll get a copy... and yes dogwood and the slips will be in there!

You would think that mountain project would be able to export all of the data in a similar manner considering it is all stored in a database.

Could be a cool build and i bet something a lot of ppl would find very useful.

Actually getting the data from MP.com was fairly straightforward. You can do a search by grade (and by area/canyon) or even sort by grade and then choose the printer friendly view. A quick highlight and copy/paste into excel and there is the base of your data. Cleaning it up and getting it all organized is another story though! I remember having some little quirky way I had to do some of it... The SR guidebook is going to be even harder to organize because it all comes in as one column. I'm hoping that I can delete at least 50% of the SR stuff becaue I already have it from MP.com. Should have it done by may at the very latest!

Great work! I'd take a copy of the sheet when you're done if you don't mind.

You guys are nerds. I mean this in the most disrespectful way imaginable. I will give each of you a wedgie when or if I ever see you again.

I'm not joking. (That's me thumping you in the back of the head)

AMEN.

tenesmus wrote:You guys are nerds. I mean this in the most disrespectful way imaginable. I will give each of you a wedgie when or if I ever see you again. I'm not joking. (That's me thumping you in the back of the head)

Well lets just hope you die of some african disease before you make it back then... ;) Seriously though, don't let pride get in your way, just ask and I'll share!

I'd like a copy, Please.

Much thanks to Tristan Higbee (and others who offered help)! I got the index from the SR guide into excel with very few text problems. but wow, I have 1283 cells to clean up. here is a sample of what they look like as of now (all one column):

Point of Ignition 5.11b * ................................. 150
Point ofview 5.1 1b ** ......................... .. ....... 150
Pointman 5.1 lb * ............... ............ ................. 37
Red Sky at Night 5.11b ** .................... .. .......2 15
Reef on This 5.11b * ......................................... 28
Revelation. The 5.11b/c * .................................1 99
Roofus Variation 5.1 lb * ........ ................ ....... 387

There is some cleaning up and I have to get the name, rating, and stars into their own column

If anyone has any excel or data formatting tips to seperate the name, rating, and stars it would be appreciated. I only know of Excel's ability to remove "x" number of characters from the beginning or end of cells. Nothing as smart as what I need. It may be a long tedious process... Oh, well too much snow to climb anyway...

Tyler King wrote: Well lets just hope you die of some african disease before you make it back then... ;) Seriously though, don't let pride get in your way, just ask and I'll share!

I'm sharing all right.... I got this really great African Gam-Boo from my gut that I'm going to wipe into your nostrils.

heh heh. Seriously, I'm giving you a noogie.

It shouldn't take too long to get that pretty close to being finished.
You can do a find and replace on . to eliminate all of the dots.

The formula =right(cell reference,4) will give you the 4 characters to the right of the cell. That should split out the page numbers.

Goto "Text to columns" under the Data menu. Choose delimited. Check other and enter 5 in the box. That will split the ratings from the route name. Then add 5. back to the rating by using ="5."&cell reference.

That should not take too long and get you really close. Send me the file if you have any problems.

Oh and I am pretty new to SLC, so I have a long Wasatch tick list. Sign me up for a copy of the finished product too.

bognish wrote:It shouldn't take too long to get that pretty close to being finished. You can do a find and replace on . to eliminate all of the dots. The formula =right(cell reference,4) will give you the 4 characters to the right of the cell. That should split out the page numbers. Goto "Text to columns" under the Data menu. Choose delimited. Check other and enter 5 in the box. That will split the ratings from the route name. Then add 5. back to the rating by using ="5."&cell reference. That should not take too long and get you really close. Send me the file if you have any problems.

Ya, I found the replace function and it was extremly useful. I also used the "CONCATENATE" function to combine stuff back together. The "cell reference" seems like it would have been useful (from "=right(cell reference,4)") I have only used the "Remove specified number of characters from the right or left side of text" stuff. I wondered how to do what you described.

Now I am left to do what Excel can't help me with; I need to add the canyon, area (maybe only the cayon for now), and whether the climb is sport, trad, or aid. Then merge the S Ruckman with MP.com (???) Actually I need to add it first and then fill in the blanks....

tenesmus wrote: I'm sharing all right.... I got this really great African Gam-Boo from my gut that I'm going to wipe into your nostrils. heh heh. Seriously, I'm giving you a noogie.

tenesmus - I'll have all the climbs in SLC ticked off my list and have moved away long before you're ever admitted back into the states!