• Welcome to Overclockers Forums! Join us to reply in threads, receive reduced ads, and to customize your site experience!

excel question

Overclockers is supported by our readers. When you click a link to make a purchase, we may earn a commission. Learn More.
I'm trying to figure out how to sort this table - the "PRI_NAME" column by name or "TOUR_CLASS" by name/number without losing the data that's associated with the values for each row - e.g "nubeena road" or "c626" without losing the values that belong to them on either side. Also, what do you think "COMP_LEN" refers to - I know it's likely distance, but maybe in metres? The "midland highway" is about 200km long, but when I do a SUM on all values under that name, I get 178707.7. Oh, just one more thing - any quick and easy way to get the distance for each route number (e.g the total distance of "c626")?
 
Ok, to sort click on the cell in the top left corner. It is blank, right above the 1, left of the A. When you click that it will select the whole sheet. Then go to the data menu at the top and go to sort. Select which column you want to sort by and click OK. That will sort the sheet but keep everything with what it should be. If you mess up undo is your friend (ctrl-z).

COMP_LEN. Who knows what it could be. I guess it would be a length of some kind. 200km is 200,000m. I've done the conversions for all different types and meters is the closest one to 178,707. But it is really impossible for us to know.

Easy way to get distance for each route number. Yes, first sort the sheet by the column you want to get your route number for. So for c626 you would sort the whole sheet by the tour_class column. Then select the whole sheet again (should be already but just incase) and go to data then to subtotals.

At each change in: Tour_Class
Use function: Sum
Add subtotal to: Comp_Len
Replace current subtotals: Check
Summary below data: Check
Click OK

You'll come back to the sheet but there will be numbers in the top left that look like little buttons. Should be a 1 and a 2 and a 3. Click the 2. This shows you the totals of each route. The subtotaling added up each c626 comp_len number and totaled them up.

If you want to remove all the subtotals you can select the whole sheet and go to data then subtotals and click the button that says remove all.

I hope this helps.
 
Back