+ Reply to Thread
Results 1 to 7 of 7

Sorting rows into numerical order while keeping gaps

  1. #1
    Registered User
    Join Date
    12-22-2014
    Location
    London
    MS-Off Ver
    20163
    Posts
    13

    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. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    739

    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. #3
    Registered User
    Join Date
    12-22-2014
    Location
    London
    MS-Off Ver
    20163
    Posts
    13

    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
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    739

    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. #5
    Registered User
    Join Date
    12-22-2014
    Location
    London
    MS-Off Ver
    20163
    Posts
    13

    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. #6
    Registered User
    Join Date
    12-22-2014
    Location
    London
    MS-Off Ver
    20163
    Posts
    13

    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. #7
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    739

    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
    Last edited by PeteABC123; 12-22-2014 at 05:53 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-08-2014, 07:05 AM
  2. sorting a column in numerical order
    By affj in forum Excel General
    Replies: 2
    Last Post: 07-19-2013, 11:13 AM
  3. Replies: 9
    Last Post: 05-24-2013, 01:31 PM
  4. [SOLVED] Paste rows with gaps between them in certain order (VBA)
    By limebaish in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-31-2013, 05:56 PM
  5. Replies: 2
    Last Post: 10-13-2011, 04:05 PM

Bookmarks

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