+ Reply to Thread
Results 1 to 7 of 7

Automatically Order/Sort a pricing table based on rank

  1. #1
    Registered User
    Join Date
    02-23-2013
    Location
    Florida
    MS-Off Ver
    Excel For MAC 16.28
    Posts
    3

    Red face Automatically Order/Sort a pricing table based on rank

    I have a pricing catalog from that has about 70 items/products/services
    If I create a quote for a customer based on those 70 items (and select say 5 or 10 items), I want the resulting pricing table to have all the items in a specific order.
    I have ranked the "order" of preference in the catalog.

    The attached workbook has:

    1) Catalog with the rankings/preference
    2) Sample Raw Pricing Table
    3) Goal: how I want things to appear (the order or items).

    Note that it would also be good to have the Total cost...so somehow, the total cost would appear at the next blank row...

    Thanks
    Villagechief
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Automatically Order/Sort a pricing table based on rank

    ???

    Why not, on Raw Pricing Table... select d1.

    Data/Sort&Filter/AtoZ
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    02-23-2013
    Location
    Florida
    MS-Off Ver
    Excel For MAC 16.28
    Posts
    3

    Re: Automatically Order/Sort a pricing table based on rank

    Hi Glenn,

    Sorry, in reviewing my post I might not have been as clear, I would like to have it populate in the sorted automatically on another sheet ("Goal"). I think the key word is automatic.

    Villagechief

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Automatically Order/Sort a pricing table based on rank

    IMHO your request WAS ambiguous...

    The basic formula in A2:
    =IFERROR(INDEX('RAW PRICING TABLE'!A:A,AGGREGATE(15,6,ROW('RAW PRICING TABLE'!$A$2:$A$30)/(SMALL('RAW PRICING TABLE'!$D$2:$D$30,ROWS($1:1))='RAW PRICING TABLE'!$D$2:$D$30),1)),"")

    can be copied across and down. However for column B, this is needed:

    =IF(AND(C2="",C1<>""),SUM($B$1:B1),IFERROR(INDEX('RAW PRICING TABLE'!B:B,AGGREGATE(15,6,ROW('RAW PRICING TABLE'!$A$2:$A$30)/(SMALL('RAW PRICING TABLE'!$D$2:$D$30,ROWS($1:1))='RAW PRICING TABLE'!$D$2:$D$30),1)),""))
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Automatically Order/Sort a pricing table based on rank

    I have assumed that your Excel version is 2010 or later. I do not what version "Excel For MAC 16.28" represents.

  6. #6
    Registered User
    Join Date
    02-23-2013
    Location
    Florida
    MS-Off Ver
    Excel For MAC 16.28
    Posts
    3

    Re: Automatically Order/Sort a pricing table based on rank

    Upon re-reading, ambiguous is the right word! In any case, your solution works perfectly and thank you very much.
    I solved the problem at about 95% using a combination of Index and Match functions, however, you introducing SMALL is really cool.

    Many Thanks!

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Automatically Order/Sort a pricing table based on rank

    No problem! You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. How to rank, with only visible and any sort order
    By edk74 in forum Excel General
    Replies: 3
    Last Post: 07-19-2018, 03:55 AM
  2. [SOLVED] Sort table in descending order based on a column
    By excellearner121 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-23-2018, 01:15 AM
  3. Rank table and return values to the left in alphabetical order
    By kirbster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2014, 08:31 AM
  4. Placing competitors in rank order based on two scores
    By Gemsie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-10-2013, 04:59 PM
  5. Automatically sort in date order
    By k57skye in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2013, 04:21 PM
  6. Rank Table in descending order
    By Merv in forum Excel General
    Replies: 6
    Last Post: 12-17-2009, 01:22 PM
  7. Automatically sort and re-order?
    By CarrieHFx in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-26-2008, 08:56 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