#1
|
|||
|
|||
Highlight with Maching function as Pair
I want to highlight/match between column C,D & K,L. but there is a catch. As you can see on Green marked area B&C ""13710 36"18"" (they are a pair) must match in the list of K,L ""13710 36"18"" column as a pair.
so Green marked pair C,D matches with K,L. But in K,L there is another pair RED ""13710 44"18"" that doesnt match with any pair in C,D and RED ""13710 32"18"" C,D is not available in K,L so i want to highlight those who matches as a pair. which pair doesnt match wont highlight (or highlight vice versa) matching will be done between this four colums |
#2
|
||||
|
||||
Perhaps a CF like
Code:
=ISNUMBER(SEARCH(TEXTJOIN("",,$B6,$C6),TEXTJOIN("",,$K$6:$L$157)))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
this solution works. but has a problem even if B6 C6 is empty it says true. And if C6 is partially matches it says true. like
$K$6:$L$157 range has "13710 44"18" and TEXTJOIN("",,$B6,$C6 "13710 44"1" ("8" missing) |
#4
|
||||
|
||||
OK try
Code:
=ISNUMBER(MATCH(CONCATENATE(B6,C6),CONCATENATE($K$6:$K$157,$L$6:$L$157),0))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
now everything is false.
|
#6
|
||||
|
||||
I would appreciate
1.first telling me if what I proposed is what you want, even partially ( a small " thank you" does not hurt) 2. attaching a sheet instead of a useless picture would spare me some time 3.As you can see in the attached it works perfectly and, no, not "everything is FALSE"
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
sorry for the mistake. thank you very much for your time and talent spend on me.
it seems i had to ctrl+shift+enter. i didnt know i had to do that. u didnt specify {}. thank you very much. |
#8
|
||||
|
||||
Quote:
It is right when you use the formula in a spreadsheet ( using 365 myself, I often assume everyone does..) However, it is wrong when you use it in a CF as in that case the formula is automatically treated by XL as an array formula in all versions
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
||||
|
||||
A little suggestion to make things more robust. It doesn't matter with your sample data but it might matter with different data.
In columns B&C you might have: 1381 and 38"24 and in columns K & L you might have somewhere: 13813 and 8"24 When the two pairs are concatenated they both look the same: 138138"24 but they're not actually a match but the current formula would say it is a match. That can be corrected by changing from: Code:
=ISNUMBER(MATCH(CONCATENATE(K6,L6),CONCATENATE($B$6:$B$440,$C$6:$C$440),0)) Code:
=ISNUMBER(MATCH(CONCATENATE(K6,"¬",L6),CONCATENATE($B$6:$B$440,"¬",$C$6:$C$440),0)) 1381¬38"24 and the other: 13813¬8"24 so they aren't a match any more. It doesn't have to be that ¬ character, a space would be almost as good; any character that you wouldn't expect to see in the data would be best. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Compair two pair of cells: function usage | and_woox | Excel | 5 | 01-31-2023 01:12 AM |
Restrict Editing function disable insert textbox function | IanM_01 | Word | 5 | 11-21-2015 02:29 AM |
Is there a function to easily and quickly highlight? | Verbum | Word | 3 | 02-05-2013 10:09 AM |
mapping from token to text/number pair | JettyGuy | Word | 0 | 08-13-2011 10:47 AM |
find - reading highlight - highlight all / highlight doesn't stick when saved | bobk544 | Word | 3 | 04-15-2009 03:31 PM |