• 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

Status
Not open for further replies.

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
 
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)))

View attachment 75182
Welcome
The table in which the lines are required to be converted into columns, to which other tables have been added, in the same form as the attached table with different materials
I want to modify the equations to include the vertical table data of the added tables
 

Attachments

bosco_yip

Excel Ninja
Welcome
The table in which the lines are required to be converted into columns, to which other tables have been added, in the same form as the attached table with different materials
I want to modify the equations to include the vertical table data of the added tables
Your new requirement and attachment appear difference from your original post #01

In according to the forum's rule, one question one post, you are requested to open a new thread for your new question.

This post closed

Regards
 
Status
Not open for further replies.
Top