the Wasatch Range Climbs By Rating spreadsheet

If you already have the area/canyon information for the MP routes, you should be able to add the canyon with a vlookup formula for the Ruckman routes.

'Concatenate' should work the same as the &, but is harder to spell. Use the & symbol like a + sign to combine two columns.

If you keep the page numbers you could use those to roughly sort the routes by area. Maybe even pull in the table of contents and use it for vlookups.

If only I could spend more time climbing than using excel...

Ok excel master bognish,

I went through and cleaned up the page numbers. The text didn't come in 100%. Now I want to apply a canyon based on a range of page numbers. i.e. if page# is somewhere between 49-196 then "Big Cottonwood Canyon" should be the return. If I could "filter" the data with a range of numbers I could manually do it pretty easy, but I don't see how that is possible. I checked out the vlookup but it seems overcomplicated for this. Can you give me some example code for this scenario?

I'm interested in a copy.

Here is some example code to finish the spread sheet, use an if statement along with an AND. Change the value "C3" to be the cell with the page number, and adjust the ranges accordingly for the other canyons.

I tried copying and pasting from excel and the less than and greater than signs are being parsed as XML, so you'll need to replace GT with > and LT with < when you copy and paste into excel.

=IF(AND(C3 GT 49,C3 LT 196), "BCC",
IF(AND(C3 GT 200,C3 LT 290),
"LCC",IF(AND(C3 GT 295,C3 LT 305),"Ferguson","Unkown Canyon")))

Ok, I took your example from above to start with. Get the page numbers all in one column. Some have a space in them so they are not numbers. Do a find on a space, and replace it with nothing. Now all of the page numbers are numbers. If there are any text in the column multiply the column by 1 and it will turn it to a number. If that doesn’t work, the next step probably won’t either.

Once you have the page numbers fixed the easiest way to get your canyon reference would be to sort the list by page number and manually copy in the canyon name.

If you want to get fancy with a formula use an If statement with the ‘And’ function to get the greater than/less than. Try this assuming the page numbers are in column e and they are all numbers:

=IF(AND(E1 gt 49,E1 lt 196),"BC",IF(AND(E1 gt 195,E1 lt 300),"LC",IF(E1 gt 299,"LPP","PC")))

This will give you a “BC” for page numbers 50 though 195, a “LC” for pages 196 through 299, “LPP” for pages over 300, and a PC if its below 50.

Looks like Bridger1 scooped me on this one, but we are doing the same thing.

Thanks gents. I didn't know you could use IF and AND operators like that. Is there a number of statements I can have in an equation? I have the equation that covers all of the canyons but it isn't working. If I remove about half from the end it works. If I remove half form the beginning it works. I don't think it is a specific entry because my tests overlap. Here is the code:

=IF(AND(J3 GT/EQ 15,J3 LT 19),"City Creek",IF(AND(J3 GT/EQ 19,J3 LT 26),"Parley's",IF(AND(J3 GT/EQ 26,J3 LT 31),"Grandeur Peak",IF(AND(J3 GT/EQ 31,J3 LT 35),"MillCreek",IF(AND(J3 GT/EQ 35,J3 LT 40),"Neff's",IF(AND(J3 GT/EQ 40,J3 LT 49),"Mount Olympus",IF(AND(J3 GT/EQ 49,J3 LT 197),"BCC",IF(AND(J3 GT/EQ 197,J3 LT 219),"Ferguson",IF(AND(J3 GT/EQ 219,J3 LT 221),"The Hound's Tooth",IF(AND(J3 GT/EQ 221,J3 LT 389),"LCC",IF(AND(J3 GT/EQ 389,J3 LT 407),"Bell's",IF(AND(J3 GT/EQ 407,J3 LT 421),"Big Willow",IF(J3 GT/EQ 421,"Lone Peak")))))))))))))

Why don't you turd-gurgglers just open your guidebook and LOOK at the routes by grade section? For crying out in the night, you'll see so much you weren't looking for you won't believe it.

What ever happened to browsing through the book anyway? Staying up late all psyched and then going out and doing the things?

Nerds.

You can only do 7 levels of nested if statements. Beyond that either split the formula or use vlookups.

tenesmus wrote:Why don't you turd-gurgglers just open your guidebook and LOOK at the routes by grade section? For crying out in the night, you'll see so much you weren't looking for you won't believe it. What ever happened to browsing through the book anyway? Staying up late all psyched and then going out and doing the things? Nerds.

By far the best post on in the Utah forum on mp.com. Is there a prize for something this epic?

tenesmus wrote:Why don't you turd-gurgglers just open your guidebook and LOOK at the routes by grade section? For crying out in the night, you'll see so much you weren't looking for you won't believe it. What ever happened to browsing through the book anyway? Staying up late all psyched and then going out and doing the things? Nerds.

How do you think I do it? However, the "Routes by Grade Section" is missing alot of important detail such as: Where the route is, what type of climb (trad,sport, etc..), and ALL of the climbs that are not in the book and are on MP.com or elsewhere. It's kind of a pain honestly. So, I end up missing a bunch of climbs I might have otherwise climbed. Clay, quit being an old timer that refuses to look for improvement because "that's not how we did in back in the day"! (disclaimer: i mean old timer in the most endearing of ways, and not that all old timers have that mentality, just having fun/venting to Clay). I will still get psyched the night before looking through the book(or MP.com). This just makes it a lot easier. Do me a favor and save your comments for me in person when you get back and I can give you a noogie (or maybe a real nice belay) for being a smart ass.

Cmon Tyler, give Ten some hell. It's OK to be mean--especially on line when people have no idea what kind of inflections you put of words. Makes for some interesting dialog.

But...Shouldn't this whole thread be on some sort of computer geek site? Gees.

Die thread already!

I have spent some more time on the spreadsheet and am almost done! The last big thing I have to is merge the SR data with the mp.com data. I went through the 'A's (after sorting) and looked for the same name and copied the Stuart R. "stars" and "page number" to the mp.com row and then deleted the SR row. Is there a better way to do this than manually? Keep in mind they may not be exactly the same name.

Yes there is one more good trick for what you need. Turn on some good music, and find a comfy chair. Then manually sort the list. Thats gonna take some time.

Almost done!!! I need a way to distribute the file. I could email it to everyone if you send me your email address (send it to tyking at hmgsl dot com). This may be the easiest, so shoot me your email and I'll send the spreadsheet and a pdf for printing sometime next week. If you already emailed me, please do so again to this address.

I'm soooo stoked for the season to begin!!!

The email has been sent...