Find matching ID in several sheets and highlight difference in data

[LaKartoffelita]

Hi everybody,

I can't find a simple way of resolving the following problem myself:
(Using Excel 2007, PC, Windows 7)

(The solution in this thread is very close to what I am looking for, but I wasn't able to edit it to work with my data: http://www.mrexcel.com/forum/excel-...atching-id-then-compare-other-values-row.html)

I have a workbook with 4 sheets. On the first I have data, that I want to compare to the other 3 sheets and then highlight the data that's different.
These three sheets are reports from consecutive month (e.g. P1/January, P2/February, P3/March ) so if possible it would be great to have different colors, with the color for the most updated report (in this case March) overriding the other colors. (Or maybe use different formatting: grey filling for changes in P1, red font for changes in P2 and bold font for changes in P3 - I don't know how complicated that would be and I don't want it to become to complex)

I've create a google sample sheet, I hope that's ok:
(In there I added a fifth sheet to show what the result should look like)

https://docs.google.com/spreadsheets/d/145VM84ae9flqMTOb_tA9-2AiiXSI-TP_0-RU67XGdW4/edit?usp=sharing

Thanks!

 

[LaKartoffelita]

Thanks for the suggestion, but it doesn't work in my file.

Since I needed to start working with my file, I used the solution from another thread I mentioned in my first post - this wasn't 100% what I needed, but it got the work done.
So thanks for your time and help!

 

[MarcelBeug]

You're welcome, but I'm still curious why my solution was not working in your file.
In case data would be in different rows, you could have tried the following conditional formatting rules

Red: =AND(A1<>INDEX(ChangesData,MATCH($A1,INDEX(ChangesData,,1),0),COLUMN()),A1=INDEX(P1Data, MATCH($A1,INDEX(P1Data,,1),0),COLUMN()))
Blue: =AND(A1<>INDEX(ChangesData,MATCH($A1,INDEX(ChangesData,,1),0),COLUMN()),A1=INDEX(P2Data, MATCH($A1,INDEX(P2Data,,1),0),COLUMN()))
Green: =AND(A1<>INDEX(ChangesData,MATCH($A1,INDEX(ChangesData,,1),0),COLUMN()),A1=INDEX(P3Data, MATCH($A1,INDEX(P3Data,,1),0),COLUMN()))

 

[LaKartoffelita]

Well, I'm pretty sure I did something wrong, because this option didn't work either.

Here's what I did:
I activated sheet "Results" with P3 data, clicked on "New Rule", chose "Use formula to determine which cells to format" and copy pasted your formula. (I then changed all the commas to semicolons; I always need to do this to make formulas work, don't know why). I then chose a format (blue/red/green font) and clicked on OK.
I did this three times, each time using the corresponding formula and ticked "Stop if true".

I did these exact steps with the first three formulas as well.

Does that help figuring out why it didn't work??

 

[MarcelBeug]

Hi,

The comma and semicolon are related to settings: typically a semicolon is used in regions like Europe and a comma in regions like America.
As a matter of fact I'm adjusting my semicolons to commas when posting on this forum!

Well here is the code for red directly copied, with semicolons:

=AND(A1<>INDEX(ChangesData;MATCH($A1;INDEX(ChangesData;;1);0);COLUMN());A1=INDEX(P1Data; MATCH($A1;INDEX(P1Data;;1);0);COLUMN()))

Please verify if the range is set correctly at "Applies to" in conditional formatting. In the example file: =$A$1:$F$11.

This MUST work. at least with me it did: I tested successfully with adjusted sorting and removed lines.

Hope this helps.

Regards,
Marcel

 

[LaKartoffelita]

Hi,

The comma and semicolon are related to settings: typically a semicolon is used in regions like Europe and a comma in regions like America.

Hi Marcel,

thanks for the info and sorry for my late answer.
I tried the code with the semicolons, but it still doesn't work. I'm sure it's a tiny (and probably stupid) thing I am not doing right or that I am missing... I even used my sample data in order to not having to changes the range, but still didn't get any result.

Sorry!
Ann