Forum Discussion

Andrew Dejong's avatar
Andrew Dejong
Copper Contributor
Jan 26, 2018

Productionflow from row to columns

For a project there is a list of 6 product in which product is given state of producing.

Want to make the list to date of start prep, prod and done. see list on right.

Sharts

NR 1 2 3 4 5 6              
01-jan     prepare prepare prepare prepare       NR prepare Produce Done
02-jan   prepare prepare prepare prepare prepare       1 03-jan 08-jan 10-jan
03-jan prepare prepare prepare prepare prepare prepare   =>   2 02-jan 04-jan 07-jan
04-jan prepare produce prepare prepare prepare prepare       3 etc    
05-jan prepare produce prepare prepare prepare produce       4 etc    
06-jan prepare produce prepare prepare prepare produce       5 etc    
07-jan prepare done prepare produce prepare produce       6 etc    
08-jan produce done prepare produce prepare done              
09-jan produce done produce produce produce done              
10-jan done done done done done done              
Charting
Excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Jan 26, 2018
    MATCH finds the first hit so can be used to return the row number:
    =MATCH("prepare",B1:B10,0)
    INDEX gives the nth row of a column, so:
    =INDEX(A1:A10,MATCH("prepare",B1:B10,0))
    Of course you'd put "prepare" in a separate cell, not in the formula, but you get the idea.
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 26, 2018
    MATCH finds the first hit so can be used to return the row number:
    =MATCH("prepare",B1:B10,0)
    INDEX gives the nth row of a column, so:
    =INDEX(A1:A10,MATCH("prepare",B1:B10,0))
    Of course you'd put "prepare" in a separate cell, not in the formula, but you get the idea.

Resources