+ 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
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,777

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

    ???

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

    Data/Sort&Filter/AtoZ
    Glenn



  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
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,777

    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
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,777

    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
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,777

    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)

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