Find matching ID in several sheets and highlight difference in data

LaKartoffelita

New Member
Joined
Apr 8, 2015
Messages
13
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!
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
LaKartoffelita,

Thanks for the Private Message, and, the new thread.

And, for the workbook.

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)

In the following screenshot (columns B, C, and, D are hidden).

Your results are in columns H thru M, based on the above quote, and, mine are in columns O, and P.

Here is a screenshot with my understanding of your quote:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>E</th><th>F</th><th>G</th><th>H</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style=";">region</td><td style=";">position</td><td style="text-align: right;;"></td><td style=";">ID</td><td style=";">region</td><td style=";">position</td><td style="text-align: right;;"></td><td style=";">region</td><td style=";">position</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">12345</td><td style=";">Europe</td><td style=";">abc</td><td style="text-align: right;;"></td><td style="text-align: right;;">12345</td><td style="color: #FF0000;;">Asia</td><td style=";">abc</td><td style="text-align: right;;"></td><td style="color: #6AA84F;;">Asia</td><td style="color: #6AA84F;;">abc</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">23456</td><td style=";">Asia</td><td style=";">def</td><td style="text-align: right;;"></td><td style="text-align: right;;">23456</td><td style="color: #0000FF;;">Europe</td><td style="color: #0000FF;;">abc</td><td style="text-align: right;;"></td><td style="color: #6AA84F;;">Europe</td><td style="color: #6AA84F;;">abc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">34567</td><td style=";">America</td><td style=";">gih</td><td style="text-align: right;;"></td><td style="text-align: right;;">34567</td><td style=";">America</td><td style=";">gih</td><td style="text-align: right;;"></td><td style=";">America</td><td style=";">gih</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">45678</td><td style=";">Australia</td><td style=";">jkl</td><td style="text-align: right;;"></td><td style="text-align: right;;">45678</td><td style="color: #FF0000;;">Europe</td><td style="color: #FF0000;;">abc</td><td style="text-align: right;;"></td><td style="color: #6AA84F;;">Europe</td><td style="color: #6AA84F;;">abc</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">56789</td><td style=";">Europe</td><td style=";">mno</td><td style="text-align: right;;"></td><td style="text-align: right;;">56789</td><td style="color: #6AA84F;;">Australia</td><td style=";">mno</td><td style="text-align: right;;"></td><td style="color: #6AA84F;;">Australia</td><td style=";">mno</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">98765</td><td style=";">Asia</td><td style=";">pqr</td><td style="text-align: right;;"></td><td style="text-align: right;;">98765</td><td style=";">Asia</td><td style="color: #6AA84F;;">gih</td><td style="text-align: right;;"></td><td style=";">Asia</td><td style="color: #6AA84F;;">gih</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">87654</td><td style=";">America</td><td style=";">stu</td><td style="text-align: right;;"></td><td style="text-align: right;;">87654</td><td style=";">America</td><td style="color: #FF0000;;">mno</td><td style="text-align: right;;"></td><td style=";">America</td><td style="color: #6AA84F;;">mno</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">76543</td><td style=";">Australia</td><td style=";">vwx</td><td style="text-align: right;;"></td><td style="text-align: right;;">76543</td><td style=";">Australia</td><td style=";">vwx</td><td style="text-align: right;;"></td><td style=";">Australia</td><td style="color: #6AA84F;;">vwx</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">65432</td><td style=";">Asia</td><td style=";">yza</td><td style="text-align: right;;"></td><td style="text-align: right;;">65432</td><td style=";">Asia</td><td style="color: #0000FF;;">bcd</td><td style="text-align: right;;"></td><td style=";">Asia</td><td style="color: #6AA84F;;">bcd</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">54321</td><td style=";">Europe</td><td style=";">bcd</td><td style="text-align: right;;"></td><td style="text-align: right;;">54321</td><td style=";">Europe</td><td style=";">bcd</td><td style="text-align: right;;"></td><td style=";">Europe</td><td style=";">bcd</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">before</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">your results</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">?????</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Changes</p><br /><br />

Are my results correct based on the above quote?
 
Last edited:

LaKartoffelita

New Member
Joined
Apr 8, 2015
Messages
13
Hi hiker,

maybe I should have described this a bit more detailed. You're results are not quite what I need. Here's an example:

The data can change more than 1 time - in P1, P2 and in P3. So if for example the data in column E changes in P1 I need it to be colored red. But if it also changes in P2 I need it to change to blue, so it signals, that the latest change was in P2. The change in P1 in this case is not of interest for me anymore. But if there is only a change in P1 then I need it to stay red. The reason behind this is, that I then need to find what exactly has changed - was it a "real" change (Europe to Asia) or maybe just the correction of a typo. And if everything is colored green, I'd have to look through all three sheets.

Does that make it more clear?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
LaKartoffelita,

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 )

1. Would a workbook ever have worksheet Changes, and, the 12 months (P1 thru P12)?

2. Are the color changes only taking place in worksheet Changes?
 

LaKartoffelita

New Member
Joined
Apr 8, 2015
Messages
13

ADVERTISEMENT

Hi,

1. No. I look at the data quarterly. So it's always 3 months.
2. I haven't thought about that, to be honest. It would probably be convenient if the color changes take place in sheets P1 to P3 as well, but it doesn't necessarily have to.

thanks by the way for all the work you've already put into solving my problem!
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
LaKartoffelita,

I look at the data quarterly. So it's always 3 months.

3. For each quarterly workbook, are the P worksheets labeled P1, P2, and, P3?

4. Or, for quarter 2, P4, P5, and, P6?
 

LaKartoffelita

New Member
Joined
Apr 8, 2015
Messages
13

ADVERTISEMENT

I am the only one working with this data, so if it is easier to always label the sheets P1, P2 and P3 that's fine.
But I am also eager to learn, so editing the code every quarter for example to change P1 to P4 etc. would be fine for me as well!
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
LaKartoffelita,

You requirements/suggestions/ideas keep changing.

I have exceeded the normal amount of time I allocate for solving problems/requests from web sites like MrExcel.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 

LaKartoffelita

New Member
Joined
Apr 8, 2015
Messages
13
Hi,

my ideas/suggestions etc. are not changing. I think maybe you misunderstood me - I am still looking for a solution to the problem explained in the first post. I am merely open to flexible solutions, since I don't have enough experience with macros etc. to know which solution might be the easiest and because my data allows me to be flexible.

But thanks anyway for your time and for looking into it!
Maybe someone else has an idea.
 

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Assuming your data is aligned - i.e. you can just compare the same cells in different worksheets – I popose the following solution.

First define names for your data: for the example data I created names:
ChangesData
P1Data
P2Data
P3Data
each for range $A$1:$F$11 in the corresponding worksheet.

In worksheet Results – which has the same data as worksheet P3 (?) - apply the following Conditional Formatting rules, each for range $A$1:$F$11, in below order and with the “Stop if true” checked:
Code:
Red (last change in P1): =AND(A1<>INDEX(ChangesData,ROW(),COLUMN()),A1=INDEX(P1Data,ROW(),COLUMN()))
Blue (last change in P2): =AND(A1<>INDEX(ChangesData,ROW(),COLUMN()),A1=INDEX(P2Data,ROW(),COLUMN()))
Green (last change in P3): =AND(A1<>INDEX(ChangesData,ROW(),COLUMN()),A1=INDEX(P3Data,ROW(),COLUMN()))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top