|
comparing two text ranges
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 |
comparing two text ranges
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. -- Cheers, Shane Devenshire "crainone" wrote: 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 |
comparing two text ranges
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... "ShaneDevenshire" wrote: 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. -- Cheers, Shane Devenshire "crainone" wrote: 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 |
comparing two text ranges
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) "crainone" wrote: 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... "ShaneDevenshire" wrote: 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. -- Cheers, Shane Devenshire "crainone" wrote: 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 |
comparing two text ranges
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... "Toppers" wrote: 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) "crainone" wrote: 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... "ShaneDevenshire" wrote: 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. -- Cheers, Shane Devenshire "crainone" wrote: 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 |
comparing two text ranges
|
| All times are GMT +1. The time now is 09:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com