Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How can I fix this Excel LAMBDA formula to result all SKUs and prices, type of Unpivot formula

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

enter image description here

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)

SKU FLC FLP FLU
99999 100 0 20
12345 48 24 2
67890 0 0 50

>Solution :

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))
)
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading