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

# Highlight rows if the value has changed

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

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

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?

Zhaoyang

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

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

Thanks a lot Joe!

It really works!