+ Reply to Thread
Results 1 to 11 of 11

Automatically sorting rows based on two columns

  1. #1
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    71

    Automatically sorting rows based on two columns

    Hi,

    I'm looking for a function that automatically sorts the rows in the ranking table.
    The ranking criteria is based on the set difference (from highest to lowest values) and if there are ties (same values) in the set difference, there should be looked at the goal difference value as the second ranking criterium.

    I attached the file to make it more clear.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Automatically sorting rows based on two columns

    I think the easiest solution is to tweak your SET DIFF formula to include the tie breaker. I changed the formula in B15 to:

    =AM15-AW15+0.00001*BN15

    Your number formatting keeps the result looking the same, but the actual value includes the GOAL DIFF as a tiny decimal to break any ties. Fill down for the other players. You can then change the RANK formula in D15 to:

    =RANK.EQ(BE15,BE$15:BG$19,0)

    ...to get a ranking that will automatically update. If you want to reproduce a sorted table that automatically updates, just do a lookup based on the RANK. In my attachment, I use the following in I3:

    =INDEX(I$15:I$19,MATCH($D3,$D$15:$D$19,0))

    Fill it down for the other teams, then copy it to L3, X3, and the rest for your other categories. Everything should automatically update as your set data changes. Take a look at the attachment to see if it'll work for you:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    71

    Re: Automatically sorting rows based on two columns

    This looks very promising at first sight. Thanks, Cantosh. I will add reputation to your account. I will look into it next week, since I can't atm. If all goes well, and I think it will, I will mark this thread as solved.

    Btw, smart thinking about the tie breaker!

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Automatically sorting rows based on two columns

    Glad to help, I hope it works for you. Enjoy your weekend!

  5. #5
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    71

    Re: Automatically sorting rows based on two columns

    One tiny thing though about the tie breaker. If the data set changes and the set difference becomes 0, the plus sign remains because of the tiny decimal value so it gives "+nothing" instead of "0". Could this be solved by cell formatting?

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Automatically sorting rows based on two columns

    You could format it the regular way - so negative signs show for negatives and nothing shows for positives, then use a Conditional Formatting rule to show the plus for positives. Select BE15:BE19, then create a CF rule with the following formula:

    =INT(BE15)<>0 SEE EDIT BELOW

    For the Conditional Formatting, choose Format > Custom > +#;-#;0

    This should apply the +/- formatting for values in SET DIF that aren't zero, disregarding the tiebreaker decimal.

    EDIT:

    Use the following formula instead so that it includes entries with zero set diff. and negative goal diff.:

    =INT(ABS(BE15))<>0
    Last edited by CAntosh; 05-30-2017 at 12:50 PM.

  7. #7
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    71

    Re: Automatically sorting rows based on two columns

    Thanks a ton! Thread marked as solved.

  8. #8
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Automatically sorting rows based on two columns

    My pleasure, good luck!

  9. #9
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Automatically sorting rows based on two columns

    OP messaged me with some issues regarding +/- signs showing up in cells with a Set. Diff. of 0, so we're reviving the thread. Here's what I currently have:
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-01-2015
    Location
    Antwerp
    MS-Off Ver
    2019
    Posts
    71

    Re: Automatically sorting rows based on two columns

    Issue solved. The issue with the "negative nothing" was solved with =INT(ABS(BE15))<>0 (see edit post above by CAntosh) and the issue with the "negative zero" was solved by putting the default cell formatting to "number" (with no decimals) instead of "general".
    Thanks

  11. #11
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Automatically sorting rows based on two columns

    I'm glad you got it sorted out, good luck!

+ 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. sorting columns based on row criteria, with variable number of columns
    By bardobhb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-02-2014, 04:02 PM
  2. [SOLVED] alphabetic sorting of 4 columns when one of the columns has 2 rows
    By jnribbit in forum Excel General
    Replies: 10
    Last Post: 08-05-2013, 09:45 PM
  3. VBA Sorting Rows automatically
    By reyearthnexus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-19-2012, 11:38 PM
  4. Rows not automatically sorting with RTD
    By zbojnik in forum Excel General
    Replies: 0
    Last Post: 09-11-2012, 03:53 PM
  5. Sorting rows based on values in columns
    By midoindeedo in forum Excel General
    Replies: 1
    Last Post: 08-01-2012, 11:05 AM
  6. Sorting rows based on columns
    By ecology in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-22-2009, 07:32 PM
  7. Replies: 1
    Last Post: 06-23-2005, 02: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