# change schedule mode

Status
Not open for further replies.

#### Mamdouh Elfors

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

#### Attachments

• 10.5 KB Views: 9

#### 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: 11

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

#### Mamdouh Elfors

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

• 27.5 KB Views: 1

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