• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

change schedule mode

bosco_yip

Excel Ninja
Try

1] In Schedule 2 "Product" A17, formula copied down :

=LOOKUP(ROW(A1),COUNTIF(OFFSET($B$2:$M$2,,,ROW($1:$12),),">0")+1,A$3:A$13)&""

2] In Schedule 2 "Raw Material" B17, formula copied down :

=IF(A17="","",INDEX($2:$2,RIGHT(AGGREGATE(15,6,ROW(A$3:A$13)/1%+COLUMN(B:M)/(B$3:M$13>0),ROW(A1)),2)))

3] In Schedule 2 "Quantity" C17, formula copied down :

=IF(A17="","",INDEX(B$3:M$13,MATCH(A17,A$3:A$13,0),MATCH(B17,B$2:M$2,0)))

75182
 

Attachments

Peter Bartholomew

Well-Known Member
You mentioned the difference between traditional Excel and 365 in your other discussion. This somewhat underlines the point:
Code:
= LET(
   n₁, ROWS(Schedule),
   n₂, COLUMNS(Schedule)-1,
   k,  SEQUENCE(n₁*n₂,1,0),
   i₁, 1+QUOTIENT(k,n₂),
   i₂, 1+MOD(k,n₂),
   material,   INDEX(Schedule[#Headers], 1+i₂),
   product,    INDEX(Schedule, i₁, 1),
   quantity,   INDEX(Schedule, i₁, 1+i₂),
   unpivotted, CHOOSE({1,2,3}, product, material, quantity),
   FILTER(unpivotted, quantity>0) )
The formula exists within a single cell (in this case E17)
75214
 
Top