4 Replies Latest reply on Jun 10, 2016 2:19 PM by Zhaoyang Jiang

    Highlight rows if the value has changed

    Zhaoyang Jiang

      Hello Forum,

       

      How could I highlight or only filter to the rows which value have changed?

      As shown in the picture below.

      I'd like to highlight Ben, David and Ellen.

       

      Also attached workbook.

       

      Any help is appreciated!

       

       

      Zhaoyang

        • 1. Re: Highlight rows if the value has changed
          Simon Runc

          Hi Zhaoyang,

           

          Like this?

           

           

          I'm afraid I no longer have T9.2, so the attached is in 9.3. The formula I used was

           

          [Highlight if changed]

          IF(ISNULL(LOOKUP(ATTR([Code]),-1))) OR ATTR([Code]) = LOOKUP(ATTR([Code]),-1) THEN 1 ELSE 0 END

           

          I've brought this onto the colour-shelf, and set compute using - Table (Across)

           

          The ISNULL checks for the the first entry (where there is no -1) and the rest just checks the ATTR (i.e. that cell value) against the previous one (-1 in the LOOKUP)....btw you could bring this onto the filter shelf, if you wanted to filter our the changes (or the things that haven't changed)

           

          Hope this is what you need, and makes sense. Please post back if not and we can either amend the formula...or I can add a little more in the way of explanation

          2 of 2 people found this helpful
          • 2. Re: Highlight rows if the value has changed
            Zhaoyang Jiang

            Hi Simon,

             

            Thanks for the quick response.

            This is a smart way to hight light the changed cell!

             

            However, is there any way to hight light the entire row? Something looks like this

             

            So, on your workbook, i changed the calculation to "Compute using Name"

            It looks like this

            The problem is why the last '102' for Cindy is highlighted and can we highlight Ellen's row?

             

            Thank you for your help!

             

            Zhaoyang

            • 3. Re: Highlight rows if the value has changed
              Joe Oppelt

              Use the same principle Simon used.  Create a calc

               

              [Highlight if changed]

              IF(ISNULL(LOOKUP(ATTR([Code]),-1))) OR ATTR([Code]) = LOOKUP(ATTR([Code]),-1) THEN 1 ELSE 0 END

               

              Then create another one:

               

              [any changes in this row?]

              if window_sum([Highlight if changed])> 0 then "changed" else "no changes" end

               

              Put the second calc on the color shelf.  The whole row with be colored for "Changed" if even one value in the row changed.

              1 of 1 people found this helpful
              • 4. Re: Highlight rows if the value has changed
                Zhaoyang Jiang

                Thanks a lot Joe!

                It really works!