+ Reply to Thread
Results 1 to 18 of 18

Dynamic table header replicating max no. of occurrences per row across table, sorted

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Dynamic table header replicating max no. of occurrences per row across table, sorted

    Hi,

    I am constructing a table header (Table 4) which replicates characteristics from another table like this:

    Table 1 contains various combinations of clothing found in Table 2.
    Each of the type of clothings used in combinations in Table 1 have a color which is listed in Table 2.
    Each of the clothings may be listed several times (but with a different item number).
    Each of the clothings may be "sold out" or "available".
    Each combination (i.e. row) of Table 1 will have a certain sum of counts per color, for each clothing that is available with that color.
    The highest number of occurrences of a color which is found across the various combinations will be used to create the table header for Table 4.
    Table 4 will replicate each color in alphabetically sorted order in as many replicates as there are maximums found in Table 1.
    There will also be a multiplicator that may be used to expand Table 4 by doubling, tripling etc. of the maximum numbers.

    I have created an example which can be found in the attached workbook. My challenge is to create Table 4 as a single horizontal array formula without going through Table 3 or other helper columns. Is anybody able to solve this?

    Best regards,
    Marbleking

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    I suggest to solve this with pivot table.

    In that case the data needs to be restrectured.

    Is that acceptable for you?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    Hi! I was using a pivot table earlier, but need a formula this time.

    Regards,
    Marbleking

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    No problem, but I always want to understand why?

    What you gonna achieve with the horizontal array?

    And why is a helper column not allowed?

    I find helper columns in a lot of times very usefull.


    Edit

    Table 4 will replicate each color in alphabetically sorted order in as many replicates as there are maximums found in Table 1.
    You want a horizontal array with colors.

    There are NO colors added in your table 1.
    Last edited by oeldere; 08-16-2020 at 08:31 AM.

  5. #5
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245
    Quote Originally Posted by oeldere View Post
    No problem, but I always want to understand why?

    What you gonna achieve with the horizontal array?

    And why is a helper column not allowed?

    I find helper columns in a lot of times very usefull.


    Edit



    You want a horizontal array with colors.

    There are NO colors added in your table 1.

    Hi! I don’t need the colors. But I need a formula because the data set I’m working on is too large to restructure in a meaningful way. And I’m working on building a workbook where I ultimately want to turn the formulas into VBR. But I need to build a prototype piece by piece to be sure I get the correct logic and setup. Hope this makes sense.

    Regards,
    Marbleking

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    Please Login or Register  to view this content.
    That can be done, e.g. with VBA or Pivot Query.


    To show you the power of power table, see the attached file.

  7. #7
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    Thanks, Oeldere. Pivot Tables are great, indeed. I sought out Power Pivot etc. for my data earlier, but have come to the conclusion that I need a formula solution at this stage. Also, it would be really interesting to see if it was possible to solve it that way.

    Regards,
    Marbleking

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    Hi,

    =TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("",,REPT("</b><b>"&$F2:$L2,$D12*MOD(LARGE(10^6*(1+INT((SEQUENCE(COUNTA($F2:$L2)*ROWS($B3:$D10))-1)/ROWS($B3:$D10)))+MMULT(0+MID(MID(TEXTJOIN("",,TEXT(TRANSPOSE(COUNTIFS($B18:$B45,FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,$B3:$D10)&"</b></a>","//b"),$C18:$C45,$F2:$L2,$D18:$D45,"Available")),"00")),6*(SEQUENCE(COUNTA($F2:$L2)*ROWS($B3:$D10))-1)+1,6),{1,3,5},2),{1;1;1}),SORT(SEQUENCE(,COUNTA($F2:$L2),,ROWS($B3:$D10)),,-1,1)),10^6))),"</b><b>","",1)&"</b></a>","//b"))

    Note that FILTERXML is not available in Excel Online.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    @XOR LX; that's one formidable formula right there! First time I've seen FILTERXML put to use. I copied it into the workbook, but unfortunately it produces #N/A. I have MS 365 and FILTERXML is available. Regards

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742
    Can you upload the updated workbook with my formula included?

    Regards

  11. #11
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    Quote Originally Posted by XOR LX View Post
    Can you upload the updated workbook with my formula included?

    Regards
    Please see attached!

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

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

  13. #13
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    @Bo_Ry! What a beautiful formula - it works effortlessly! Thanks a lot!

  14. #14
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    Re my formula, you've translated it incorrectly. It's unfortunate that most online formula translators aren't capable of correctly translating the separators within array constants.

    The formula I posted included:

    {1,3,5}

    which you have mistranslated as:

    {1;3;5}

    Not sure what the separator for horizontal arrays in your version of Excel is - perhaps the backslash? If so, that part should be, for you:

    {1\3\5}

    Regards

  15. #15
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    @XOR LX; according to these Microsoft articles, the array constant separators should be the same; commas for rows, semicolons for columns:

    https://support.microsoft.com/en-us/...d-fcae47454eb8

    https://support.microsoft.com/nb-no/...d-fcae47454eb8

    I guess I need to change my Excel version; after all most people in this forum uses the one you use, with commas instead of semicolons as argument separators in functions.

    I'm sure if you attach the workbook with the formula it will be translated correctly! I, unfortunately, still get an error when I try to substitute the operators.

    Regards,
    MK

  16. #16
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    I wouldn't trust those articles. It appears that Microsoft have lazily translated the English version word for word into each additional language version: the Italian version, for example, gives comma and semicolon as well, though I know for a fact that that is not the case!

    Besides, you can perform a simple check yourself. Enter =A1:A3 and =A1:C1 somewhere in the worksheet and evaluate each by going into the formula bar, highlighting the formula and pressing F9.

    I get:

    {0;0;0}

    for =A1:A3, and

    {0,0,0}

    for =A1:C1.

    I wouldn't go as far as to say you need to change your version of Excel! You simply need to be aware of the array constant separators for your version, and know that online translators do not account for this part. If you really want to work with English language settings, you can simply change it in Time & Language/Additional date, time, & regional settings/Change date, time, or number formats. That will give you the English versions for argument separators and also array constant separators. In fact, that's precisely how I guessed at your separators, by setting that option to Norwegian.

    Of course, we can also use functions for the two static array constants that I used, to avoid any confusion, i.e.:

    =TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("",,REPT("</b><b>"&$F2:$L2,$D12*MOD(LARGE(10^6*(1+INT((SEQUENCE(COUNTA($F2:$L2)*ROWS($B3:$D10))-1)/ROWS($B3:$D10)))+MMULT(0+MID(MID(TEXTJOIN("",,TEXT(TRANSPOSE(COUNTIFS($B18:$B45,FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,$B3:$D10)&"</b></a>","//b"),$C18:$C45,$F2:$L2,$D18:$D45,"Available")),"00")),6*(SEQUENCE(COUNTA($F2:$L2)*ROWS($B3:$D10))-1)+1,6),SEQUENCE(,3,,2),2),SEQUENCE(3)^0),SORT(SEQUENCE(,COUNTA($F2:$L2),,ROWS($B3:$D10)),,-1,1)),10^6))),"</b><b>","",1)&"</b></a>","//b"))

    Regards

  17. #17
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    @XOR LX; thanks for advice. I tried your test and it turned out that your
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    must be written
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in my version - and voila, it works effortlessly!

    Now I'll sit down and digest all the good input to get a grip on the inner workings of these formulae. Thanks again!

    Regards

  18. #18
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Dynamic table header replicating max no. of occurrences per row across table, sorted

    [Post Deleted]
    Last edited by XOR LX; 08-17-2020 at 02:41 AM.

+ 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: 38
    Last Post: 09-26-2018, 07:06 AM
  2. Creating unique sorted list from dynamic table - Formula Solution Still Required
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-15-2018, 01:27 AM
  3. Dynamic reference to Table Column header
    By aiyathomas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2017, 10:30 PM
  4. Dynamic Table based on Header
    By markusvirus in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-23-2017, 04:06 AM
  5. [SOLVED] Map Columns With Different Header Names Using A Dynamic Table
    By jlanderson18 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2016, 09:04 AM
  6. dynamic excel table header / column selection
    By carsto in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-27-2016, 11:25 AM
  7. Replies: 0
    Last Post: 10-10-2015, 09:51 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