Highlight changed values week on week basis

[vaibhav tandon]

Hi all,

I am trying to compare to sets of data pulled from tool to highlight the changes that Project Managers have made week on week basis as compared to last week. Tab 'old' has last week data while tab 'New' has data. I used formula in conditional formatting "=A1<>Old!A1" but this is only limited if both sheets have the data arranged in same manner. But since there are new projects added each week there is reordering of projects and gives me wrongly highlighted cells.


For example E got added this week in 'new' tab and comparing it with row 5 of 'old' tab and giving wrong highlighted cells.

The longer method i could think of is to rearrange all the data and apply the same formula however due to time contraints its not viable since the data lines are almost 10000.

Could any one suggest a better way of highlighting the changed cells?

Thanks in advance for helping!

 

[vletm]

vaibhav tandon
If Column A & B are those which are same in both sheets...
Press [Do It] and
You'll see changes ... and comment would show 'Old's value.

 

[r2c2]

@vaibhav tandon

I was about to post something and see that @vletm has come up with a VBA solution.

If you want something based on CF, use this approach.

1. Remove the existing rules from New sheet.
2. Select only C2:G7 range (as these are the only things that can change), you can select more rows if you want.
3. Go to CF > New Rule and type in =index(Old!C:C,match($a2,Old!$A:$A,0))<>C2
4. Apply formatting as you want.
5. Done.

This works as long as column A is unique (ie no duplicate primary_portfolio names).

 

[vletm]

vaibhav tandon
... oops ... there are 10000 lines and all differences would need to get.
... then it needs this (much faster)!

 

[vaibhav tandon]

vaibhav tandon
If Column A & B are those which are same in both sheets...
Press [Do It] and
You'll see changes ... and comment would show 'Old's value.

Hi Vletm,

Thanks for the solution but I am not well versed with VBA and just a starter in that so it might be difficult for me to understand and change the code( in case there are changes). Thanks for all the help and time you have given to my problem. God Bless you!

 

[vaibhav tandon]

@vaibhav tandon

I was about to post something and see that @vletm has come up with a VBA solution.

If you want something based on CF, use this approach.

1. Remove the existing rules from New sheet.
2. Select only C2:G7 range (as these are the only things that can change), you can select more rows if you want.
3. Go to CF > New Rule and type in =index(Old!C:C,match($a2,Old!$A:$A,0))<>C2
4. Apply formatting as you want.
5. Done.

This works as long as column A is unique (ie no duplicate primary_portfolio names).

Hi R2C2, the unique column is B instead of A, I have made the changes in the formula and in the first look it seems its working like a charm( would check with all the data with 10000 rows and 35 columns). Thanks for all the help and time. God Bless you both r2c2 and vletm!

 

[vletm]

vaibhav tandon
What would mean 'might be difficult'?
The code would be ready, if You would wrote about 35 columns.
r10000c35 is 'nice range to have conditional format'.
... it's Your choice.

 

[vaibhav tandon]

Hi Vletm,

Trust you are fine!

Sorry was away and could not see the post.

I meant that if I had to make any changes it would have been difficult for me. I have used the code and it works like charm seriously! Just click of a button and it highlights everything!

God bless you for all the help!

Regards
Vaibhav