hi,
i have 2 sheets on the file attached and I would like to highlight the differences on the stocklist sheet compared to the sheet1 , using column "c" as the reference
hi,
i have 2 sheets on the file attached and I would like to highlight the differences on the stocklist sheet compared to the sheet1 , using column "c" as the reference
Hi Steve,
Try the attached.
Last edited by AB33; 03-04-2014 at 10:32 AM.
you can use
=COUNTIF(Stocklist!$C:$C,$C1)>0
for 2007 or 2010 excel version
Conditional Formatting
Highlight applicable range >>
C:C
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIF(Stocklist!$C:$C,$C1)>0
Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
if you apply to sheet 1 , this will highlight all the cells that do not match sheet1 with the stocklist sheet based on column C
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
hi , the attached file is good thank you
I have just though that the other way around could have problems for me therefore could you please check to see if there are items is sheet1 that are not in sheet " stocklist"
thank you both for your help
then apply to column C on the stocklist
and change the count to
=COUNTIF(Sheet1!$C:$C,$C1)>0
now it will look down the stocklist and highlight where they match
OR
change to
=COUNTIF(Sheet1!$C:$C,$C1)=0
where they do not match
The reverse of the code is attached below the code and highlight the difference in sheet1.
=match would be the function to use to compare data from two different data sets.
List A (base data)
list B (data to compare)
=match(lookupvalue,lookuprange,0) 0 = exact match
i.e.
=match('ListA'!A1,'ListB'!C:C,0)
then just run this down but keep the range the same
B1 & 'ListB'!C:C
C1 & 'ListB'!C:C
Last edited by Sc0tt1e; 03-04-2014 at 11:23 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks