+ Reply to Thread
Results 1 to 10 of 10

Dynamic array to replace pivot table (sorting problem)

  1. #1
    Registered User
    Join Date
    04-30-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    5

    Question Dynamic array to replace pivot table (sorting problem)

    Hello Excel comunity,

    I started playing with dynamic arrays to replace PivotTables. It s working well but I am struggling to order the data in the way I need.
    I wonder if is possible to make this Dynamic Array table without using the Auxiliar Table.

    Here is an example:

    1624666365574.png

    Formulas:
    1)=UNIQUE(Data[Customer])
    2)=SUMIF(Data[Customer],D4#,Data[Amount])

    3)=SORTBY(D4#,E4#,-1)
    4)=SUMIF(Data[Customer],D14#,Data[Amount])

    Thanks,
    Nicolas.
    Attached Files Attached Files
    Last edited by Noneto; 06-26-2021 at 01:25 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,600

    Re: Dynamic array to replace pivot table (sorting problem)

    How about using PowerQuery?

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,096

    Re: Dynamic array to replace pivot table (sorting problem)

    Hi Nicholas,

    according to Forum Rule #3 you should add a link to the other thread(s) you posted on different forum(s) on the same topic (i.e. https://www.mrexcel.com/board/thread...oblem.1174855/).

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    04-30-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    5

    Re: Dynamic array to replace pivot table (sorting problem)

    Thanks! I just updated the sample workbook.

    I'm using Dynamic arrays formulas because they update instantly, With power query and pivot table I need to refresh them

  5. #5
    Registered User
    Join Date
    04-30-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    5

    Re: Dynamic array to replace pivot table (sorting problem)

    Thanks for your comment.

    I am not allow yet to add links: "You are not allowed to post any kinds of links, images or videos until you post a few times."

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,600

    Re: Dynamic array to replace pivot table (sorting problem)

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. The link has been added for you this time in post #3.)

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Dynamic array to replace pivot table (sorting problem)

    I don't know how to make it work with a dynamic array in a SINGLE one-size fits all type of step... but you certainly do not need that intermediate table:

    For customer, here in G3:

    =IFERROR(INDEX(Data[Customer],MATCH(H3,SUMIFS(Data[Amount],Data[Customer],Data[Customer])*(COUNTIF($G$2:$G2,Data[Customer])=0),0)),"")

    For SUM amount, here in H3:

    =IFERROR(1/(1/AGGREGATE(14,6,SUMIFS(Data[Amount],Data[Customer],Data[Customer])*(COUNTIF(G$2:G2,Data[Customer])=0),1)),"")

    both copied down as far as needed.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 06-26-2021 at 02:57 AM.
    Glenn



  8. #8
    Registered User
    Join Date
    04-30-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    5

    Re: Dynamic array to replace pivot table (sorting problem)

    Thanks for the Welcome.
    I will read the Rules in detail before posting again!

  9. #9
    Registered User
    Join Date
    04-30-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    5

    Re: Dynamic array to replace pivot table (sorting problem)

    Thanks Glenn for your reply.

    Your formulas achieved the job but are not a dynamic array (the formula won't expand / contract the table automatically).


    I found the solution in the other post.
    Here the soluciton:
    =LET(cust,Data[Customer],uc,UNIQUE(cust),SORTBY(uc,SUMIF(cust,uc,Data[Amount]),-1))

    Or

    =SORTBY(UNIQUE(Data[Customer]),SUMIF(Data[Customer],UNIQUE(Data[Customer]),Data[Amount]),-1)
    Last edited by AliGW; 06-26-2021 at 03:52 AM. Reason: PLEASE don't quote unnecessarily!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,600

    Re: Dynamic array to replace pivot table (sorting problem)

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who 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. pivot table sorting problem
    By elham.kh88 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-25-2020, 11:04 AM
  2. [SOLVED] Pivot sorting problem when table has two columns
    By SwissExcel in forum Excel General
    Replies: 5
    Last Post: 12-08-2016, 03:39 PM
  3. Pivot Table Sorting Problem
    By Excel8449 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 08-09-2016, 02:49 PM
  4. Pivot Table Sorting Problem
    By kevsvette in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-02-2015, 03:24 PM
  5. sorting problem w/ pivot table
    By jyl7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-22-2013, 06:30 PM
  6. Pivot table- sorting with value filter problem
    By Masun in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-02-2013, 03:55 AM
  7. Regarding Pivot table sorting problem
    By ramki in forum Excel General
    Replies: 3
    Last Post: 08-06-2009, 02:36 AM

Tags for this Thread

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