Compare values from 2 colums and highlight only repeated values in col A.
EG:
Col A Col B
1001 1001
1001 1001
1001
Only the first 2 will be highlighted in Col A.
I'm trying to compare physical stocks on hand against those in my system.
Thank you in advance for your help!
just use a conditional formatting rule
Select column A
use a formula for the conditional formatting rule
=$A1=$B1
that will also highlight all the blank cells - so to avoid that
=AND(A1=B1,OR(A1<>"",B1<>""))
then if the cell A or B is blank - it will be ignored
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 etaf,
Thank you so much for replying!
But when I include more values to test, it all got highlighted.
Attached is my working file.
just us
=AND(A1=B1,OR(A1<>"",B1<>""))
not both formulas
Hi etaf,
Sorry, I'm not sure where I'm doing it wrong but its still not showing the results in the "after"..
Sorry about that..
that looks OK when I open it
What are you after
Before
Cell highlighted are A2 , A3 , A8
which is correct as they match the same value in cells B2, B3 , B8
or did you want something different in the After Sheet?
Hi etaf,
What I'm looking for is if there are three "1001" in column B, three "1001" should be highlighted in column A.
Following the formula, only 2 got highlighted in column A instead of 3.
Thank you for your time!
thanks so thats different to what I thought you had asked originally
try
=AND(A1<>"",COUNTIF($A$1:A1,A1)<=COUNTIF($B:$B,A1))
Hi etaf!
Thank you so much! My problem has been solved!!!
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks