comparing two text ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to see which text entries in one array are listed in another
array. I have tried the =countif(array2,$d2)=0 method within conditional
formatting and it only seems to select some of the fileds some of the time
and I cant seem to understand where the problem that keeps it from only
selecting some of the correct fields. I can validate and see that it selects
some of the correct answers sometimes, but is not complete. Please help.
Thanks, Chris
 
Hi,

Give us a small sample of your data. By array you mean "range"?

The standard way to do this would be:

1. Select one range to check
2. Choose Format, Conditonal Formatting, Formula Is
3. Enter the following formula =OR($A1=$E$1:$E$9)
where A1 is the first cell in the range to be checked and E1:E9 is the range
to check against.
 
Shane,

Thanks for the quick reply, however, this seems rather simple and there are
lots of methods for finding those values that apply to my initial query.
Unfortunately, the results are sporadic.

The data are similar to this:
Column A - called array1 Column B - called array2

MUNDO-TECH INC BJG ELECTRONICS, INC
MUSE METAL LABORATORIES, INC MUNDO-Tech Inc.
MYONIC Natinal Hybrid
NAMEPLATE MFG OF AMERICA INC HERLEY-VEGA SYSTEMS
NAMEPLATE MFRS OF AMERICA National Instruments
NAPCO INTERNATIONAL INC TYCO ELECTRONICS
NATIONAL HYBRID INC Myonic
NATIONAL INSTRUMENTS ARROW ELECTRONICS INC

In this example using conditional formatting and your =or($a4=$B2:$B$14000),
most of these show up and those that don't change formatting, are actually,
really on list B... I don't understand why some of the data are highlited
(formatted correctly based on finding a match) and some are not. I'm trying
to see those values in column A that are also in Column B.
Unfortunatetly,the results are truly sporadic...
 
Extra blanks will cause mismatches but these may not be obvious from a visual
inspection.

Try TRIM function on data you believe should match but doesn't e.g
=TRIM(A100)=TRIM(B50)
 
I actually validated some of the data with and without the trim function and
a few of those that are highlited say that when checked =a6192=b4329 end up
true
Thanks for the suggestions so far...
 
Sorry to take so long. Could you send me a copy of a few lines that fail but
should work. You can tweek the data so that its nothing confidential.

(e-mail address removed)
 
Back
Top