Combining BYCOL and BYROW

[adidriksen]

Hello,

I am trying to convert a long table (source) to a wide table. I have written up some examples of what I'd like to accomplish, but neither of the solutions does everything I want it to.

It seems the way Excel currently behaves, I can only either have a table that adapts its height dynamically, or one that adapts its width dynamically.

Table 1 does what I want to, but I have to supply the column names and row names and the number of cell will always be fixed.

Table 2 and 3 work a bit better: Table 2 can contain more columns if more column names are spilled from H7; Table 3 can contain more rows if more row names are spilled from G18. But none of my tables are able to do both at the same time, because if I try to combine BYROW and BYCOL, I get #CALC! (Table 5).

I included Table 4 because I think it's curious that it returns an error even though it's just a part of the calculation for Table 2, maybe someone can also tell me what's wrong with that formula?

If any of you Excel Champs could help me with this it would make my day. Our infrastructure sometimes requires pivoting between long and wide formats and prohibits use of power pivot, so a formula solution would be great.

[#2 · Accepted solution]

@adidriksen 

=LET(rng,B3:D8,

VSTACK(

HSTACK("",TOROW(UNIQUE(CHOOSECOLS(rng,2)))),

HSTACK(

UNIQUE(CHOOSECOLS(rng,1)),

IFERROR(

MAKEARRAY(COUNTA(UNIQUE(CHOOSECOLS(rng,1))),COUNTA(UNIQUE(CHOOSECOLS(rng,2))),LAMBDA(r,c,FILTER(CHOOSECOLS(rng,3),(CHOOSECOLS(rng,1)=INDEX(UNIQUE(CHOOSECOLS(rng,1)),r))*(CHOOSECOLS(rng,2)=INDEX(UNIQUE(CHOOSECOLS(rng,2)),c))))),

""))))

MAKEARRAY returns the intended result in my sheet. Otherwise you can combine BYCOL and BYROW by using REDUCE which can spill horizontally and vertically at the same time.

This is the result for range B3:D12:

[#3]

@adidriksen There are various ways to accomplish this, but all will be obsolete soon when the new GROUPBY and PIVOTBY functions are released later this year (hopefully). In the meantime, one option is to use SUMIFS with both a vertical criteria list and a horizontal criteria list to spill the results in a 2D array:

=LET(
    proList, TOCOL(UNIQUE(product), 1),
    catList, TOROW(UNIQUE(category), 1),
    VSTACK(
        HSTACK("Sum of Price", catList),
        HSTACK(proList, SUMIFS(prices, product, proList, category, catList))
    )
)

See attached, if needed...

[#4]

@OliverScheurichThat was quick! Works beautifully, thanks a lot! I'm not familiar with the LET and CHOOSECOLS functions, but I'll be sure to look into them, as you've certainly demonstrated their usefulness.

[#5]

@djclementsThank you for the very efficient solution and for providing context to the topic! I was not aware that the Office team was working on specific functions for this problem, that's great news. The "spilling" functions (FILTER, UNIQUE and so on) and lambda functions recently became available at my company and they've certainly been very empowering so far. I'm very excited to see what the future brings!