5 Replies Latest reply on Jan 11, 2017 6:57 AM by Benjamin Costello

    Table Calc to identify change from prior value and to show both on single record

    Dan Gerena

      Each week I append the full employee roster into a table. What I want to do now is create a report that identifies when someone's department changes (it does not = that same employee's department from the prior week.) Thus the bolded record below...

      Then I want to filter out all other records and just show the changed value and the prior value on a single record, like step 2 below.

       

      Anyway to do both, or at least to just filter the changed records if I can't do step 2?

       

      Raw Data
      As of DateEmployee#NameDepartment
      1/1/201312345Jane DoeABC
      1/1/201322222John SmithXYZ
      1/1/201355555Mike GreenGGG
      1/8/201312345Jane DoeDEF
      1/8/201322222John SmithXYZ
      1/8/201355555Mike GreenGGG
      1/15/201312345Jane DoeDEF
      1/15/201322222John SmithXYZ
      1/15/201355555Mike GreenGGG
      Step 2: some Lag/Lead function to bring the prior
      value onto the same record as the changed value
      As of DateEmployee#NameDepartmentPrior Dept
      1/8/201312345Jane DoeDEFABC
      Only want to keep the changed rows, and their respective prior value