Forum Discussion
adidriksen
Apr 08, 2024Copper Contributor
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. ...
- Apr 08, 2024
=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
Apr 08, 2024Gold Contributor
=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
Apr 08, 2024Copper Contributor
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.