How do I highlight data differences in workbook

T

tweacle

I have a workbook which has 3 worksheets named 03 feb, 10feb-24 mar and
31 mar-19th May. All worksheets have data in columns A-K and have 220
entries. What im trying to do is to be able to match up data but where
its different in column A I need it highlighted.
I.E I have in cell A5 on 03 feb and 10 feb-24 mar the number 4 but in
cell A5 on 31 mar-19 may its different and has the number 9. What I
want to do is where its different I want it highlighted.

Can I do this. Thanks
 
B

bridgesmj

Hi, from what I understand I think this is possible; assuming that you
want the range to refer to the same row in the other sheets you could
use a simple logical IF formula, along the lines of:

=IF(OR('10feb-24 mar'!A5<>'03 feb'!A5,'31 mar-19th May'!A5<>'03
feb'!A5,'10feb-24 mar'!A5<>'31 mar-19th May'!A5),FALSE,TRUE)

Just insert it adjacent to the range and double click the fill handle.
 
B

bridgesmj

OK so I just reread the question. Once you have copied this formula
down (in column L), you need a way of knowing. You can either apply an
autofilter (alt-dff) for the FALSE values, or set up conditional
formatting to get a visual on which cells return a FALSE value.

Hope this helps.

Mark
 
R

Roger Govier

Hi

One way
Click on the first sheet tab, hold down shift and click on the last
sheet tab. This will group the sheets.
In a spare column on the sheet, enter

=IF(SUM('03 Feb':'31 mar - 19th May'!A1)<>A1*3,"Different","")
Copy down as far as required.
Now click on any sheet tab to ungroup the sheets

The word Different will show up on each row where the three value are
not the same.
 
B

bridgesmj

Roger, that's a good way - I had thought of that in my sleep somehow
and was just about to post it - minus the bit about grouping the
sheets. Hence in B5:

=IF(SUM(A5,'10feb-24 mar'!A5,'31 mar-19th May'!A5)<>A5*3,FALSE,TRUE)

Regards,

Mark.
 
Top