Forum Discussion

adidriksen's avatar
adidriksen
Copper Contributor
Apr 08, 2024

Combining BYCOL and BYROW

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.   ...
BI & Data Analysis
Excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Apr 08, 2024

    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:

djclements's avatar
djclements
Bronze Contributor
Apr 08, 2024

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...

  • adidriksen's avatar
    adidriksen
    Copper Contributor
    Apr 08, 2024

    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!

Share

Resources