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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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:


Excel 2007
AEFGHLMNOP
1IDregionpositionIDregionpositionregionposition
212345Europeabc12345AsiaabcAsiaabc
323456Asiadef23456EuropeabcEuropeabc
434567Americagih34567AmericagihAmericagih
545678Australiajkl45678EuropeabcEuropeabc
656789Europemno56789AustraliamnoAustraliamno
798765Asiapqr98765AsiagihAsiagih
887654Americastu87654AmericamnoAmericamno
976543Australiavwx76543AustraliavwxAustraliavwx
1065432Asiayza65432AsiabcdAsiabcd
1154321Europebcd54321EuropebcdEuropebcd
12beforeyour results?????
Changes


Are my results correct based on the above quote?
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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()))
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top