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

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

Regards,
Marbleking

Originally Posted by oeldere

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

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

@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

Originally Posted by XOR LX

Can you upload the updated workbook with my formula included?

Regards

Please see attached!

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

@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

@XOR LX; thanks for advice. I tried your test and it turned out that your

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

must be written

Formula:

Please Login or Register  to view this content.

and

Formula:

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