# change schedule mode

#### Mamdouh Elfors

##### Member
Hi
How do I change schedule1 mode to schedule 2 mode . with formulas
File attachment
Thanks

#### Attachments

• 10.5 KB Views: 8

#### bosco_yip

##### Excel Ninja
Hi
How do I change schedule1 mode to schedule 2 mode . with formulas
File attachment
Thanks
What Excel version now you are working ?

Or,

Do you have Textjoin or Concat function ?

#### Mamdouh Elfors

##### Member
bosco
Excel version 2016

#### Mamdouh Elfors

##### Member
bosco
You can't find these functions

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

#### Attachments

• 17.1 KB Views: 10

#### Mamdouh Elfors

##### Member
Thank you Bosco
This is what is required

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