+ Reply to Thread
Results 1 to 9 of 9

Sort array formula results in descending order

  1. #1
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Sort array formula results in descending order

    Hi All,

    Be great if someone could help on this on.

    I have main table of data that consists of a long list of products being manufactured. For each row (that represents one product), our delivery coordinator can choose day for delivery + vehicle for that day + delivery order. Sometimes more than one item my be delivered to the same customer so if the first two products are going to the one delivery address, the order entered under 'delivery order' column in this main table will be 1, 1 respectively for these two items. The next delivery may have one item being delivered so the coordinator would enter 2 and so on.


    I then have another tab/sheet called 'Transport Plan' in which the delivery coordinator needs only to enter two pieces of information at the top: date and vehicle type (from a drop down). An array formula will then return all rows from the main table that match this (i.e. all deliveries out on the 22/02/17 on Vehicle 2).

    However the delivery order entered in the main table may not necessarily be in numerical order top to bottom (depending on how he wants to juggle the deliverers)... so this means that currently the data in the Transport Plan could look like this (these are the pulled in delivery orders as found in the main table)...

    3
    1
    4
    1
    2
    2
    5


    How can I re-order this in the transport plan so will be ordered as
    1
    1
    2
    2
    3
    4
    5

    ??


    The array formula I am currently using is:

    =IFERROR(INDEX(PLANNING_SHEET_FULL_TABLE,SMALL(IF(COUNTIFS($M$2,PS_DELDATE,$M$3,PS_TPORT),ROW(PLANNING_SHEET_FULL_TABLE)-MIN(ROW(PLANNING_SHEET_FULL_TABLE))+1),ROW(A1)),MATCH(B$5,PS_PLANNING!$B$5:$AZ$5,0)),"")


    I am thinking I could use a series of helper columns, first being the regular formula, then a column with same formula but showing row number, then a third column ranking the first column, then a 4th column with 1,2,3,4,5 ect going down the rows (depending on how many rows have data) then a 5th column doing an index match to order the row numbers by rank.
    Finally I can then use an index match in the main transport plan table to use the row number as the row reference.


    Is this the way to go or is there a neater / better way?


    Thank you very much in advance!

    Work sheet attached if required.


    Kind regards.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Sort array formula results in descending order

    Hi
    Use this regular formula In PS_Transport PLAN to get the row number (Excel row, not Table row) in the order you pretend
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    So you can use index to full fill the table

    See the file

  3. #3
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Sort array formula results in descending order

    Thank you very much!

    I now seam to be struggling to use an indirect or row or address function (any of those correct) in my standard index match to use the cell reference for the row number in the index match.

    Can you suggest how best to do that.


    Thank you very much again.

  4. #4
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Sort array formula results in descending order

    I suppose INDEX is good (INDIRECT and ADDRESS only if necessary)
    Try for first column
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks for your feedback.

  5. #5
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Sort array formula results in descending order

    Thank you!

    I would never have worked that out.


    For completeness to this thread, I have adapted that to become:

    =IFERROR(IF($A7=0,"",INDEX(PS_PLANNING!$A:$AZ,$A7,MATCH('PS_TRANSPORT PLAN'!B$5,PS_PLANNING!$B$5:$AZ$5,0)+1)),"")


    I have also adapted the aggregate formula with an if criteria to not 'run' if the deliveries coordinator has not chosen the date and vehicle type at the top (as with these empty, the formula was apparently returning the relative row number based on the row from the transport list if the value in the cell were just zero)

    =IFERROR(IF($M$2="","",MOD(AGGREGATE(15,6,(ROW(PS_TPORT)/10000+PS_DELORD)/(PS_DELDATE=$M$2)*(PS_TPORT=$M$3),ROW(A1)),1)*10000),"")


    Thank you again.

  6. #6
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Sort array formula results in descending order

    Hi José,

    Sorry to bother you but I have just discovered a slight issue with this formula.


    If I have more than one vehicle out on the same date, It will first return a zero for each line that matches the date but not the vehicle in question, before it then ranks the deivery order for the correct vehicle.

    I cant work out how to get it to ignore any zeros and only return the row numbers for exact matches.

    Are you able to advice?


    Thank you very much in advance!

  7. #7
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Sort array formula results in descending order

    Try this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    =IFERROR(MOD(AGGREGATE(15,6,(ROW(PS_TPORT)/10000+PS_DELORD)/((PS_DELDATE=$M$2)*(PS_TPORT=$M$3)),ROW(A1)),1)*10000,"")

  8. #8
    Registered User
    Join Date
    03-29-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    78

    Re: Sort array formula results in descending order

    Thank you José,

    The simple addition of those brackets has fixed it immediately!

    Many thanks indeed,

  9. #9
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Sort array formula results in descending order

    I'm glad I helped.

+ 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. [SOLVED] Formula to Sort the Data in Descending Order
    By Ankit_Kumar in forum Excel General
    Replies: 8
    Last Post: 04-27-2015, 07:48 AM
  2. Automatic sort by descending order
    By anapaulacasseta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2014, 10:49 PM
  3. macro to sort in ascending/descending order
    By apio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2012, 02:46 AM
  4. [SOLVED] Sort extracted data in descending order
    By Aland2929 in forum Excel General
    Replies: 5
    Last Post: 07-24-2012, 03:14 PM
  5. Can you sort a chart out in Ascending/Descending Order
    By dandavis1 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-26-2010, 09:52 AM
  6. Macro to sort data in descending order
    By Vbort44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2008, 11:46 PM
  7. Sort in descending order
    By shahcu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2008, 03:01 AM

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