I'm trying to make a formula which will unpivot some price data, there would several extra columns of data later, including some SKU data, and then 3 columns of price, 3 columns of net price, and the discount amount/unit size for each corresponding row.

Currently, just trying to get a small set to work for the base of the formula, and I got it to work correctly, but only for 1 SKU out of the 3 in the list. How could I adjust this LAMBDA to result all 3 SKUs in this same format? Here's the LAMBDA I'm currently using:

=LAMBDA(SKU_col,FL_cols,
    LET(SCT,COUNTA(SKU_col)-2,      
        SKUA,INDEX(SKU_col,3,1):INDEX(SKU_col,SCT,1),
        FLC,INDEX(FL_cols,3,1):INDEX(FL_cols,SCT,1),     
        FLP,INDEX(FL_cols,3,2):INDEX(FL_cols,SCT,2),     
        FLU,INDEX(FL_cols,3,3):INDEX(FL_cols,SCT,3),
        SROWS,SEQUENCE(ROWS(SCT*3)),
        SR,CEILING(SROWS/3,1),
        MD,IF(MOD(SROWS,3)=0,3,MOD(SROWS,3)),
            VSTACK( HSTACK(INDEX(SKUA,SR,1),INDEX(FLC,SR,1)),
                    HSTACK(INDEX(SKUA,SR,1),INDEX(FLP,SR,1)),
                    HSTACK(INDEX(SKUA,SR,1),INDEX(FLU,SR,1))
            )))

Here's an image, I am using column A as the "SKU_col", and columns B:D as the "FL_cols". Column F:G show the current result of this LAMBDA, and Column I:J show the ideal results of this.

Edit to add the sample data (even though it is literally only 3 rows so me typing it in StackOverflow is like 5x more difficult than you just typing it into excel)

CodePudding user response：

you can use this formula:

= LET(SKU,A2:A4,FL,B2:D4,
s,MAKEARRAY(ROWS(SKU),COLUMNS(FL)*2,
     LAMBDA(r,c,IF(ISODD(c),INDEX(SKU,r),INDEX(FL,r,c/2)))),
WRAPROWS(TOCOL(s),2))

or as LAMBDA:

= LAMBDA(SKU,FL,
LET(
s,MAKEARRAY(ROWS(SKU),COLUMNS(FL)*2,
     LAMBDA(r,c,IF(ISODD(c),INDEX(SKU,r),INDEX(FL,r,c/2)))),
WRAPROWS(TOCOL(s),2))
)