Comparing Lists of text using colors or by other means

[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.

 

[Dave Peterson]

You could use a formula in column C that would indicate if the value in column B
appears anywhere in column A.

=isnumber(match(b1,a:a,0))

And drag down as far as you need (all 1000 rows).

If you see True, there's a match. If you see False, then there's no match.

You could apply data|filter|autofilter (xl2003 menus) and even filter to show
just what you wanted.

 

[Jeff M]

Thanks.. this is close to working.. is there a similar formula if the cell
contents are text (account names) versus an number?

 

[Dave Peterson]

The =match() will check to see if there's a match between any type of
value--text or number.

But if you have some entries that look like numbers, but are actually text, then
=match() won't find it.

'123
is different from
123

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

(Yep. =vlookup() won't find a match in this case, either.)