What Excel version now you are working ?Hi
How do I change schedule1 mode to schedule 2 mode . with formulas
File attachment
Thanks
= 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) )
WelcomeTry
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
Your new requirement and attachment appear difference from your original post #01Welcome
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