Comparing Lists of text using colors or by other means

  • Thread starter Thread starter Jeff M
  • Start date Start date
J

Jeff M

I have two lists of account names (for example). 500 names in column A and
1000 names in column B. I would like to highlight or somehow alter the names
in column B that are in Column A without using the find and replace
function.. I do not want to do it one at a time but cannot figure out how to
do it with the entire range all at once? Goal is to then use the data
associated with the highlighted cells in column B for reports etc.
 
Hi Jeff,

I am assuming names are identical in each list as to their spelling.
If your list starts in a1 and b1
put this formula in cell C1 (you can hide it later)

=IF(ISNA(VLOOKUP(B1,(A$1:A500),1,FALSE)),FALSE,TRUE)

500 being the length of your list or longer. Drag/copy it all the way down
or longer to the length of list B.

Highlight your ENTIRE list in B and select conditional formating.
In the formula line put

=c1=true

Then set whatever format highlight you want, such as bold characters or a
different color background. This is for office 2007. You may need to adjust
for 2000/2003.

Let me know if this works for you.

Squeaky.
 
Back
Top