Productionflow from row to columns

[Andrew Dejong]

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

[#2 · Accepted solution]

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.