Forum Discussion

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

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:

OliverScheurich's avatar
OliverScheurich
Gold Contributor
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:

adidriksen's avatar
adidriksen
Copper Contributor
Apr 08, 2024

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.

Resources