1

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.

enter image description here

Edit to add the sample data:

SKU FLC FLP FLU
99999 100 0 20
12345 48 24 2
67890 0 0 50
ZygD
  • 22,092
  • 39
  • 79
  • 102
Andy L
  • 93
  • 6
  • Please provide the data as table not as picture - then it is much easier to help you. – Ike Nov 04 '22 at 16:24
  • It is a SO practice to add sample data as table (read [ask] and [repro]) - you can use https://www.tablesgenerator.com/markdown_tables – Ike Nov 04 '22 at 16:51

1 Answers1

1

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))
)
Ike
  • 9,580
  • 4
  • 13
  • 29
  • Thank you so so much! This works perfectly! I cleaned it up a little bit for my own use, now time to add the rest of the data and hope I don't break the code LOL... Where would you recommend adding the 3 columns for the Net pricing? That's going to be my next step, as there are discounts off of the FLC (case cost) to make a new NETC (net case cost), and then I have 3 columns just to the right of where the FL is listed. – Andy L Nov 04 '22 at 16:49
  • Without seeing the data it's hard to help. – Ike Nov 04 '22 at 16:52
  • is there any way to private message? I can send you more examples or I could just edit the question, but didn't want to get in trouble for adding to the question – Andy L Nov 04 '22 at 16:57
  • You should add a new question - describing the extended requirement. – Ike Nov 04 '22 at 17:01
  • Sounds good, will add a new question right now! I'll show a bit more extended column data and include the desired output of the whole result. I really appreciate your help, was banging my head trying to figure this out lol. – Andy L Nov 04 '22 at 17:02
  • okay I posted the new question, if you're able to help would be much appreciated, if not that's okay, I'll try to review your code more in depth this weekend and see if I can adjust it to add the extra columns – Andy L Nov 04 '22 at 18:44