+ Reply to Thread
Results 1 to 26 of 26

Dynamically generated table sorted by a column

  1. #1
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Dynamically generated table sorted by a column

    Hi,

    I am looking for a dynamically generated table from source data table which shows distinct values of Supplier sorted by the total Spend per Supplier in descending order. Spreadheet with data attached.

    Thanks a lot for your for your help.

    Tomas

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,338

    Re: Dynamically generated table sorted by a column

    Cell I4 formula

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

  3. #3
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    Hi,

    your formula does not work.

    How about using modern Excel function like FILTER, SORT, UNIQUE?

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,338

    Re: Dynamically generated table sorted by a column

    Cell I4 formula

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


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

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,996

    Re: Dynamically generated table sorted by a column

    EDIT: I did not see the other posts before I posted this...

    Just a tweak to xk9128's formula to force the sorting:

    =SORT(LET(x,Table1[Supplier],y,Table1[Spend],z,UNIQUE(x),CHOOSE({1,2},z,SUMIFS(y,x,z))),2,-1)

  6. #6
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    Great, it works! Thanks a lot

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,828

    Re: Dynamically generated table sorted by a column

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

    Also, if you have not already done so, 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 all those who offered help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    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.

  8. #8
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    It does not work. I have got #VALUE! error. I do not know why.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,828

    Re: Dynamically generated table sorted by a column

    Who are you talking to???

  10. #10
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    Hello Ali,

    marked solved, I always thank users who came with working solution and increase their reputation too.
    Last edited by AliGW; 03-04-2022 at 04:53 AM. Reason: PLEASE don't quote unnecessarily!

  11. #11
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    @Gregb11

    It does not work. I have got #VALUE! error. I do not know why.
    Last edited by AliGW; 03-04-2022 at 04:53 AM. Reason: PLEASE don't quote unnecessarily!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,828

    Re: Dynamically generated table sorted by a column

    That's exactly what Iit means - thank all those who offered help, not just those whose solutions worked, but everybody who offered assistance.

  13. #13
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    Quote Originally Posted by AliGW View Post
    Who are you talking to???
    To Gregb11. Shall I allways reply with quote?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,828

    Re: Dynamically generated table sorted by a column

    No - all you need to do is start your post with @Gregb11, or similar.

  15. #15
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    @AliGW

    okay noted and will do next time.
    Last edited by AliGW; 03-04-2022 at 04:54 AM. Reason: PLEASE don't quote unnecessarily!

  16. #16
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    @AliGW Like this? Is there another way to maybe select the user I am replying to rather than type @user_name?

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,828

    Re: Dynamically generated table sorted by a column

    Yes, like that - no, there is no other way. It's not hard or onerous, though, is it???

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    35,725

    Re: Dynamically generated table sorted by a column

    Probably it's down to separator differences. Here's my formula:

    =LET(a,Table1[Supplier],s,Table1[Spend],ua,UNIQUE(a),T,SUMIF(a,ua,s),SORTBY(CHOOSE({1,2},ua,T),T,-1))

    See it in the sheet. It translates to:

    =LET(a;Table1[Supplier];s;Table1[Spend];ua;UNIQUE(a);T;SUMIF(a;ua;s);SORTBY(ZVOLIT({1;2};ua;T);T;-1))

    in Czech (according to a www site... I don't speak Czech, myself!!)

    I changed a few suppliers, etc, for testing... as the fact that the alphabetical order: A B C D E was also the corretc sum value could be misleading...
    Glenn



  19. #19
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    Quote Originally Posted by AliGW View Post
    Yes, like that - no, there is no other way. It's not hard or onerous, though, is it???
    no prob at all. Thnaks for help. I even increased your reputation. :D

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,828

    Re: Dynamically generated table sorted by a column

    You didn't need to do that - I did not help to solve your problem! But thanks ...

  21. #21
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    @Glenn

    Hi Glenn,
    I used the function with semicolon separators and I use English function names too. The problem was caused by not using backslash in CHOOSE function CHOOSE({1\2} instead of comma or semicolon separator. This is correct solution: =LET(a;Table1[Supplier];s;Table1[Spend];ua;UNIQUE(a);T;SUMIF(a;ua;s);SORTBY(CHOOSE({1\2};ua;T);T;-1)) which you actually used (with the backslash) in the Excel file you modified for me. This also prevented the similar solution from wk9128 above from working too.

    Thanks a lot!
    Last edited by AliGW; 03-04-2022 at 06:04 AM. Reason: PLEASE don't quote unnecessarily!

  22. #22
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    Quote Originally Posted by AliGW View Post
    You didn't need to do that - I did not help to solve your problem! But thanks ...
    You offered your help to me to use the forum correctly, though. That counts too. :D Have a good day!

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    O365 (PC) V 2210
    Posts
    35,725

    Re: Dynamically generated table sorted by a column

    tomas... I had forgotten about the \. It's hard to remember all these exceptions.

    Glad you're sorted!

  24. #24
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    @Glenn, just curious: is the \ something to do with regional settings in this function? I guess it must be as you both guys used comma instead.

  25. #25
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,425

    Re: Dynamically generated table sorted by a column

    Yes, it is. If your list separator is a comma, then the array separators are comma for horizontal and semicolon for vertical.
    Rory

  26. #26
    Registered User
    Join Date
    01-12-2022
    Location
    Brno, Czech Republic
    MS-Off Ver
    365
    Posts
    41

    Re: Dynamically generated table sorted by a column

    @rorya, this is what I googled out for different launguages:

    | LCID | Row | Column |
    |-------|-----|--------|
    | ar-sa | ; | , |
    | bg-bg | ; | \ |
    | cs-cz | ; | \ |
    | da-dk | ; | \ |
    | de-de | ; | \ |
    | el-gr | ; | \ |
    | en-gb | ; | , |
    | en-ie | ; | , |
    | en-us | ; | , |
    | es-es | ; | \ |
    | et-ee | ; | \ |
    | fi-fi | ; | \ |
    | fr-fr | ; | \ |
    | he-il | ; | , |
    | hr-hr | ; | \ |
    | hu-hu | ; | \ |
    | id-id | ; | \ |
    | it-it | ; | \ |
    | ja-jp | ; | , |
    | ko-kr | ; | , |
    | lt-lt | ; | \ |
    | lv-lv | ; | \ |
    | nb-no | ; | \ |
    | nl-nl | ; | \ |
    | pl-pl | ; | \ |
    | pt-br | ; | \ |
    | pt-pt | ; | \ |
    | ro-ro | ; | \ |
    | ru-ru | ; | \ |
    | sk-sk | ; | \ |
    | sl-si | ; | \ |
    | sv-se | ; | \ |
    | th-th | ; | , |
    | tr-tr | ; | \ |
    | uk-ua | ; | \ |
    | vi-vn | ; | \ |
    | zh-cn | ; | , |
    | zh-hk | ; | , |
    | zh-tw | ; | , |

    Thx

+ 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. Replies: 0
    Last Post: 10-04-2018, 05:46 AM
  2. Dynamically generated Shell command
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2016, 02:31 PM
  3. [SOLVED] Duplicate a column of data from a table into new, sorted table
    By JimDandy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2015, 12:53 PM
  4. Stop equations updating dynamically when table is sorted/filtered
    By drDanRyan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2014, 08:10 AM
  5. [SOLVED] Use VBA to download a dynamically generated image from the web.
    By nponoBegHuk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-01-2012, 09:51 AM
  6. creating pop-up menu from dynamically generated column values
    By bcamp1973 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-12-2007, 01:11 PM
  7. Dynamically Generated Range using Macro
    By JM.EE01@gmail.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2006, 03:15 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