Hi,
With reference to the below screenshots;
this is the core data sheet, whereby a 10 minute time stamp goes across the top, the line and corresponding status down the left side, and the intersection (i.e. where a value is populated), indicates the status of the line for the amount of time shown.
2018-06-20 14_56_21-OEE input sheet1 Amended.xlsx - Excel.png
I wish to convert the data shown above into a format such as this shown below, this has to be done via formula as I strictly cannot use any other means. what I am looking to populate is the status in the tall/slim table, from the short/fat one showing above.
2018-06-20 14_56_57-OEE input sheet1 Amended.xlsx - Excel.png
In essence then, and if an example is worked, the value in cell F2 would be the rest of a lookup on both line AB1 and time 08:00:00 into the core data table, and the corresponding result would be "machine set-up" from column D - as this is populated with "10" in cell E8. Moving to 08:30 (as 8am-8.20 inclusive is all machine set up), the result I would expect here would be "Line Operating". You will notice some columns are double populated - i.e. 08:40 has values populated against Line operating and Label Change - if its easy and perhaps one way of achieving this would be to select the first non-blank in the range - have this select the first non-blank - thus 08:40 would be "line operating".
Many thanks,
Bookmarks