# Sorting rows into numerical order while keeping gaps

1. ## Sorting rows into numerical order while keeping gaps

Hello.

5tw66q.jpg

I would like to sort the data in the LINK/PICTURE into numerical order (largest to smallest) by column O. But when I try to sort Manchester City and Manchester United by the figure in Column O, it places Manchester City into 'O2' and Manchester United into place 'O3', messing up the formulas.

I need to sort the data while keeping or locking A2 to 02 and A3 to O3 together for Manchester United for example so that the formulas don't miscalculate. I don't know how to do this.

Sorry if I explained it badly, I've only been using excel for a month. Please can anyone help? Thanks and Happy Christmas (:

2. ## Re: Sorting rows into numerical order while keeping gaps

Without having the workbook to try, here's my thoughts:
Before sorting, highlight your entire range (sans headings). Start your range selection in cell O2, and highlight from there). . .O2:A36. .Sort, using the arrow icon and it should keep your formulas intact. Please report back.
Pete

3. ## Re: Sorting rows into numerical order while keeping gaps

I don't know where the sort arrow icon is! I have tried to upload the spreadsheet so you can have a go.

Thanks very much for your help

4. ## Re: Sorting rows into numerical order while keeping gaps

Sort is in the DATA tab. Best bet is to reconfigure your data so that the AV For, and AV Aga source fields are represented in a single row. You've got 2 fields for each municipality which is hsing things up a bit, Try and combine with one data row. It can be two tables and you could do SUMIFS, or AVERAGEIFS where applicable.

HTH.

Pete

5. ## Re: Sorting rows into numerical order while keeping gaps

I think having everything in one row is the only way to do it (as you suggest). Then I can make a formula at then end of the row like = B42/(B42+C42)*100 + D42/(D42+E42)*100 etc and then make another column to average those out. With 20 columns instead of 10 it would work without me having to add the formula each time.

Thanks again.

6. ## Re: Sorting rows into numerical order while keeping gaps

now I made this. The totals and averages don't show up until all the data has been entered (which is ok), but it seems to work if you just put in any numbers. Then it can sort easily Column W into order.

7. ## Re: Sorting rows into numerical order while keeping gaps

motorcycle:

Place this error trapping engine into V2 and copy down:
=IFERROR(B2/(B2+C2)*U3 + D2/(D2+E2)*100 + F2/(F2+G2)*100 + H2/(H2+I2)*100 + J2/(J2+K2)*100 + L2/(L2+M2)*100 + N2/(N2+O2)*100 + P2/(P2+Q2)*100 + R2/(R2+S2)*100 + T2/(T2+U2)*100,"")

You might also try this instead, which populates partials where there are numbers:

=IFERROR(SUM(B2/(B2+C2)*U3,D2/(D2+E2)*100,F2/(F2+G2)*100,H2/(H2+I2)*100,J2/(J2+K2)*100,L2/(L2+M2)*100,N2/(N2+O2)*100,P2/(P2+Q2)*100,R2/(R2+S2)*100,T2/(T2+U2)*100),"")

Place this into W2 and copy down: =IFERROR(V2/10,"")

HTH. Let us know.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1