Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlight dups in one of two columns
I have words in column A and B. I'd like to use conditional formating to
highlight just those words in A that also occur in B. The following is close, but I believe gives a mirror image of what I want. Appreciate any help. Thanks. 1. Select column B (click the letter B) 2. Choose Format Conditional formatting 3. In ""Condition 1"" choose ""Formula is""" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<"") 5. Click the ""Formats"" button and take your pick. OK.OK. -- Alan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlight dups in one of two columns
Try this:
Assume the range of names is in A1:A10 and B1:B10 Select the range A1:A10 (don't select the entire column, it's a waste of resources to format cells that aren't being used) 2. Choose Format Conditional formatting 3. In ""Condition 1"" choose ""Formula is""" 4. Enter this formula: =MATCH(A1,B$1:B$10,0) 5. Click the ""Formats"" button and take your pick. OK.OK. Biff "adgorn" wrote in message ... I have words in column A and B. I'd like to use conditional formating to highlight just those words in A that also occur in B. The following is close, but I believe gives a mirror image of what I want. Appreciate any help. Thanks. 1. Select column B (click the letter B) 2. Choose Format Conditional formatting 3. In ""Condition 1"" choose ""Formula is""" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<"") 5. Click the ""Formats"" button and take your pick. OK.OK. -- Alan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlight dups in one of two columns
Works perfectly! Thanks for taking the time to help me.
I'd like to learn more about how this function works within conditional formatting. Is it possible to give a brief explanation? -- Alan "Biff" wrote: Try this: Assume the range of names is in A1:A10 and B1:B10 Select the range A1:A10 (don't select the entire column, it's a waste of resources to format cells that aren't being used) 2. Choose Format Conditional formatting 3. In ""Condition 1"" choose ""Formula is""" 4. Enter this formula: =MATCH(A1,B$1:B$10,0) 5. Click the ""Formats"" button and take your pick. OK.OK. Biff "adgorn" wrote in message ... I have words in column A and B. I'd like to use conditional formating to highlight just those words in A that also occur in B. The following is close, but I believe gives a mirror image of what I want. Appreciate any help. Thanks. 1. Select column B (click the letter B) 2. Choose Format Conditional formatting 3. In ""Condition 1"" choose ""Formula is""" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<"") 5. Click the ""Formats"" button and take your pick. OK.OK. -- Alan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
highlight dups in one of two columns
You're welcome. Thanks for the feedback!
I'd like to learn more about how this function works within conditional formatting. Is it possible to give a brief explanation? Sure =MATCH(A1,B$1:B$10,0) The lookup value is cell A1. The lookup array is B1:B10. 0 means we want to find an exact match. If the lookup value is found in the lookup array it returns a number that is the relative position of the lookup value within the lookup array: A1 = green B1 = black B2 = green B3 = red =MATCH(A1,B1:B3,0) returns 2 because the lookup value green is in the 2nd position relative to the lookup array. How this works in conditional formatting: In order for the format to be applied the condition must evaluate to TRUE. A formula that returns a number like the MATCH function can be used because *ANY* number other than 0 will evaluate as TRUE and cause the format to be applied. A 0 will evaluate to FALSE. An error value like #N/A which the MATCH formula would return if no match was found would also cause the format to NOT be applied. Biff "adgorn" wrote in message ... Works perfectly! Thanks for taking the time to help me. I'd like to learn more about how this function works within conditional formatting. Is it possible to give a brief explanation? -- Alan "Biff" wrote: Try this: Assume the range of names is in A1:A10 and B1:B10 Select the range A1:A10 (don't select the entire column, it's a waste of resources to format cells that aren't being used) 2. Choose Format Conditional formatting 3. In ""Condition 1"" choose ""Formula is""" 4. Enter this formula: =MATCH(A1,B$1:B$10,0) 5. Click the ""Formats"" button and take your pick. OK.OK. Biff "adgorn" wrote in message ... I have words in column A and B. I'd like to use conditional formating to highlight just those words in A that also occur in B. The following is close, but I believe gives a mirror image of what I want. Appreciate any help. Thanks. 1. Select column B (click the letter B) 2. Choose Format Conditional formatting 3. In ""Condition 1"" choose ""Formula is""" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<"") 5. Click the ""Formats"" button and take your pick. OK.OK. -- Alan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a macro to hide certain columns | Excel Discussion (Misc queries) | |||
Selecting different multiple columns | Excel Worksheet Functions | |||
compare two columns within a worksheet, then highlight duplicates | Excel Worksheet Functions | |||
"Text to Columns" for many columns in Excel 2003 | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |