2 Replies Latest reply on Aug 24, 2016 3:59 AM by J CK

    How to combine multiple column based on null and create a single column

    J CK

      Hello Data people!!!

       

      I am breaking my head to find a solution the below mentioned challenge, i have a set of data as mentioned below.

       

      These are the life stages of a particular item, I want to do a calculation which tells me, which stage my item is based on the date of stage finished.

       

      For eg: my item no# 1 is in Stage 2, based on the finished date of Stage1.

       

      I have my target or future column in green on the right.

       

      Also attached a spreadsheet of the same content.

       

      Any help is appreciated   Thank you!!!!!!  

      ItemDate_Stage1_finishedDate_Stage2_finishedDate_Stage3_finishedDate_Stage4_finishedDate_Stage5_finishedDate_Stage6_finishedDate_Stage7_finishedCurrent Stage- My target
      17/25/2016Stage 2
      27/25/2016Stage 2
      37/25/2016Stage 2
      47/25/2016Stage 4
      57/25/20167/25/20167/29/2016Stage 4
      67/25/20167/25/20167/28/2016Stage 4
      77/25/20167/25/20167/29/2016Stage 4
      87/25/20167/25/20168/10/2016Stage 4
      97/25/20167/26/2016Stage 3
      107/25/20168/10/2016Stage 3
      117/25/20168/10/2016Stage 3
      127/27/20167/29/2016Stage 3
      137/27/20167/29/20167/29/20167/29/2016Stage 5
      147/28/20167/28/20168/10/20168/10/2016Stage 5