# 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 (:  Register To Reply

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  Register To Reply

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  Register To Reply

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  Register To Reply

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.  Register To Reply

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.  Register To Reply

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  Register To Reply