NEXT CELL VALUE FROM THE SAME CODE

Copper Contributor

Hi There, in tab labelled "DATA" I need to basically transpose the info into the next "OUTPUT" tab.
Some style numbers have multiple colour options but I need these to run in rows rather than down columns.. What's the easiest way to do this? Some sort of INDEX/XMATCH?Screenshot 2024-05-16 at 13.24.09.pngScreenshot 2024-05-16 at 13.24.20.png

1 Reply

@PAULBRAGASON 

If you have 365, you can use the standard REDUCE/VSTACK method:

=LET(
    styles, SORT(UNIQUE(StyleNumber)),
    pivot, LAMBDA(acc, v,
        LET(
            record, TOROW(FILTER(ColourInfo, StyleNumber = v), , 1),
            IFERROR(VSTACK(acc, HSTACK(v, record)), "")
        )
    ),
    DROP(REDUCE("", styles, pivot), 1)
)